This post follows on from the Hello World Excel VBA post which is an introduction to the world of VBA. I urge you to quickly read that post before continuing here.
Here is the code created in that exercise:
Private Sub Workbook_Open() MsgBox "Hello World!" End Sub
The above code exists in
ThisWorkbook which is an object in the
VBA Project. The simple
MsgBox function employed in the previous example is now expanded upon:
Option Explicit Private Sub Workbook_Open() Dim userResponse As String userResponse = MsgBox("Hello World!", vbOKCancel) If userResponse = vbOK Then ' all is good - open the Workbook as usual Exit Sub Else ' user cancelled - close this Workbook ActiveWorkbook.Close End If End Sub
- The line
Option Explicitensures that all variables are declared — in the above example, this code is superfluous as no variables are declared. It is good coding practice to declare your variables.
Dimstatement declares the variable
userResponseas a string type.
- The value returned by the
MsgBox()is then assigned to the variable userReponse using the
- The value returned will be one of the constants; in this case if the OK button is clicked the constant vbOK is returned.
- The logic
If ... Then ... Else ... End Ifis a very basic logic structure that should be manageable for any Excel user familiar with the
Have a look at the official documentation for more: https://docs.microsoft.com/en-gb/office/vba/Language/Reference/User-Interface-Help/msgbox-function