Most users of MS Excel are unaware of the power and functionality of macros and the in-built Visual Basic for Applications (VBA) programming language.
Macros are good for recording repetitive tasks but many people become frustrated with them because they don't quite solve the problem being addressed.
For example, you can use a macro to create a chart but what happens if the data set is added to? You could record a macro which would select the entire column of data before creating the chart, but you might want to keep some explanatory text at the bottom of the column which might mean you need to manually select the data before using the macro to create the chart.
Use VBA To Fine Tune Your Macros You can use VBA to adapt a macro to better suit your needs.
Once your macro has been saved, you can go into the code window and edit the macro code, which just happens to be written in VBA.
If this sounds a little daunting, you're in for a nice surprise.
Because you're creating your own macro, you'll know already what the VBA code is trying to achieve.
You can experiment with the code without doing any damage - save a copy of the macro if you're concerned.
Let's try a simple example to get started on using macros and VBA together.
Don't worry about the syntax or the commands at this stage, it's just important you understand the basics.
The generated code should look something like this:
Range("a1").
select Selection.
Font.
Bold=true Because you know what you're trying to achieve with the macro, the code should appear reasonably self-explanatory.
The macro selects the cell, and then makes the cell contents bold.
Now, play around with the code see what you can change.
You could make the text italic as well by adding to the code:
Selection.
Font.
Italic=true What about something you can't easily do with a macro? Perhaps you're writing a report with tomorrow's date required in the first cell, bold and underlined.
You can create a function to do this, but this example is designed to show you that what Excel can do, you can do too.
Just add this into the code
Selection.
Font.
Underline = True Selection.
Value = Date + 1 Although this is a simple example, it should give you a few ideas as to how Excel macros and VBA can help your efficiency, perhaps even giving you time for an extra espresso throughout the day! It's good to be a little ambitious when you think about how VBA can help you; for example, instead of simply making the date bold, why not make the date red, but only on a Monday? And could you use Excel to email that report once the VBA code has completed it? Most of us use Excel every week, so what are some of the things about using Excel that frustrate you? There's a good chance that macros and VBA can provide the solution.
Macros are good for recording repetitive tasks but many people become frustrated with them because they don't quite solve the problem being addressed.
For example, you can use a macro to create a chart but what happens if the data set is added to? You could record a macro which would select the entire column of data before creating the chart, but you might want to keep some explanatory text at the bottom of the column which might mean you need to manually select the data before using the macro to create the chart.
Use VBA To Fine Tune Your Macros You can use VBA to adapt a macro to better suit your needs.
Once your macro has been saved, you can go into the code window and edit the macro code, which just happens to be written in VBA.
If this sounds a little daunting, you're in for a nice surprise.
Because you're creating your own macro, you'll know already what the VBA code is trying to achieve.
You can experiment with the code without doing any damage - save a copy of the macro if you're concerned.
Let's try a simple example to get started on using macros and VBA together.
- Type some text into the first cell of a worksheet.
- Record a macro which makes the text bold
Don't worry about the syntax or the commands at this stage, it's just important you understand the basics.
The generated code should look something like this:
Range("a1").
select Selection.
Font.
Bold=true Because you know what you're trying to achieve with the macro, the code should appear reasonably self-explanatory.
The macro selects the cell, and then makes the cell contents bold.
Now, play around with the code see what you can change.
You could make the text italic as well by adding to the code:
Selection.
Font.
Italic=true What about something you can't easily do with a macro? Perhaps you're writing a report with tomorrow's date required in the first cell, bold and underlined.
You can create a function to do this, but this example is designed to show you that what Excel can do, you can do too.
Just add this into the code
Selection.
Font.
Underline = True Selection.
Value = Date + 1 Although this is a simple example, it should give you a few ideas as to how Excel macros and VBA can help your efficiency, perhaps even giving you time for an extra espresso throughout the day! It's good to be a little ambitious when you think about how VBA can help you; for example, instead of simply making the date bold, why not make the date red, but only on a Monday? And could you use Excel to email that report once the VBA code has completed it? Most of us use Excel every week, so what are some of the things about using Excel that frustrate you? There's a good chance that macros and VBA can provide the solution.
SHARE
