Introduction to VBA
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. By using Record Macro, VBA will track and translate everything you do 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.
Learning the basics
If...Then...Else
The If...Then...Else statement in VBA is similar to If statements found in other languages, such as Pyhton or C++. The main difference is the syntax, which we will shown using some examples. Note that the conditions used in the If...Then...Else statements are further described in the chapter Operators.
Loops
Infinite loops in VBA can be exited by pressing Esc + Break. Occasionally this does not work, and you will have to force Excel to close, e.i. by pressing Ctrl + Shift + Esc and right-click on Excel. Forcing an exit may not save your current work, so make sure to save while writing.
Operators
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.
Sub example1() MsgBox "Displaying a message!" ' When we only want to display a message, we don't need several parameters. End Sub
Example 1 displayes the following dialog box:
Sub example2() Dim Msg, Style, Title, Response, MyString Msg = "Do you want to continue ?" ' Define message. Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons. Title = "MsgBox Demonstration" ' Define title. Response = MsgBox(Msg, Style, Title) ' When we have several parameters, ' we have to call MsgBox as: MsgBox([parameters]). If Response = vbYes Then ' User chose Yes. MyString = "Yes" ' Perform some action. Else ' User chose No. MyString = "No" ' Perform some action. End If End Sub
Example 2 displayes the following dialog box: