Introduction to VBA in Excel
Hello World
Our first encouter with VBA will be to display the familiar "Hello World" message using a button.
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.i. 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.
Explicit vs implicit variable declaration
When writing larger pieces of code, knowing the difference between explicit and implicit variable declaration may save you several hours of debugging.
Useful functions
Format
The Format function is a helpful tool when formatting strings to your preference. Since Microsoft already has made a really in-depth explanation including some examples, we have chosen to only show you some additional examples here.
MsgBox
The MsgBox function displays a message in a dialog box and waits for the user to click a button. You may have seen it used in our other tutorials as a simple way to display the value of a variable, but this is just the function in its simplest use. In addition to a message, you can specify e.i. the title or the buttons. The return value of MsgBox is a value (integer) indicating which button the user clicked. To learn about all the buttons and their return values, read the function documentation.