One of the quickest ways to discover
Excel VBA syntax is to record a macro. Recorded macros can be
somewhat limited and will always use literal values for objects
such as cells and worksheet names. This is often undesirable.
Fortunately, recorded macros can be modified to make them more
dynamic or to extend their functionality. The syntax of some
Excel tasks is particularly well suited to being discovered
using recorded macros:
- Copying and pasting ranges
- Inserting and/or deleting worksheets
- Opening a new workbook or moving
between workbooks
- Moving between worksheets
Recorded macros will
not contain certain VBA coding structures such as loops,
decision-making If or Select Case blocks, or With blocks.
These VBA blocks must be manually coded.
Following is a possible business scenario:
- A report is generated in a workbook.
- Each time the report is run, the refreshed data is saved off to a separate workbook.
- It is desirable to partially automate this repetitive procedure.
The following Flash Exercise shows how recording and modifying a macro can be used to solve the business scenario.
FLASH
EXERCISE: Record a macro
and modify the code
