Introduction to VBA in Excel
Hello World
Our first encouter with VBA will be to display the familiar "Hello World" message using a button.
Expand | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
Recording a macro
Even though a lot of your work in VBA both can and will be made using only programming, it is highly recommended to get to know the Record Macro feature as well. When using Record Macro, VBA will track and translate every action you perform to code. This can help you in several ways:
- It is always easier to start a task with some existing code instead of a blank sheet.
- Use the fact that VBA translates all your actions into code to your advantage. Instead of searching online for e.g. how to mark a range of cells, try recording a macro doing it and then look at the code created.
Our first task will be to duplicate a sheet using the Record Macro feature.
Expand | ||
---|---|---|
| ||
|
Expand | ||
---|---|---|
| ||
Making a function
In the previous tutorials, we have made procedures using the Sub statement. We will now look at how we can make a procedure using the Function statement. First, let's have a quick look at an embedded function. SUM(number1;number2...) (SUMMER in Norwegian) is a function that sums up all its parameters. This function is reachable for execution from a cell in our sheet (picture).
We will now make our very own custom function that can be adressed the same way as SUM, but the calculations made are specified by us.
Expand | |||||
---|---|---|---|---|---|
| |||||
|
Useful applications of VBA in Excel
Duplicating charts between sheets or workbooks
When duplicating (copying) charts between different sheets or workbooks, a common issue is that the new chart still refers to the old table, also if we don't want it to. However, this is easily fixed using a rather simple code. A video illustrating our problem is shown below. Our aim is to duplicate the chart (as well as the theme and settings of the chart) showing the temperatures and make it show the rainfall instead.
BibTeX Display Table |
---|