Another Excel VBA Example
In this Excel VBA Example we'll see how to cause code to be run when Excel opens.
Copy the following code - drag your mouse from the first letter to the last letter or from the last to the first:
Dim Answer As StringOpen a new Excel workbook and press Alt + F11 to open the Visual Basic Editor. In the VBAProject pane at the upper left, double click ThisWorkbook...
Dim Sentence As String
On Error Resume Next
Answer = InputBox("What day is it?" & vbCrLf & _
"1 = Sunday" & vbCrLf & _
"2 = Monday" & vbCrLf & _
"3 = Tuesday" & vbCrLf & _
"4 = Wednesday" & vbCrLf & _
"5 = Thursday" & vbCrLf & _
"6 = Friday" & vbCrLf & _
"7 = Saturday" & vbCrLf _
, "Excel VBA Example", 1)
On Error GoTo 0
If Answer = "1" Then
Sentence = "Sunday"
ElseIf Answer = "2" Then
Sentence = "Monday"
ElseIf Answer = "3" Then
Sentence = "Tuesday"
ElseIf Answer = "4" Then
Sentence = "Wednesday"
ElseIf Answer = "5" Then
Sentence = "Thursday"
ElseIf Answer = "6" Then
Sentence = "Friday"
ElseIf Answer = "7" Then
Sentence = "Saturday"
Else
MsgBox "A number from 1 to 7 was not entered"
Exit Sub
End If
Range("A1") = Sentence
'Stop copying here
At the top of the right pane, where we type code, there are two dropdown or combo boxes as they are called. Open the left one and choose "Workbook." The default choice for the right was is "Open." If you see something else there, open it and choose "Open."
Two lines should have appeared there:
Private Sub Workbook_Open()
End Sub
Right click at the beginning of the empty line between them and click Paste. The code you copied from above should now be between the two lines.
From this Excel VBA example we will see that when we open ThisWorkbook (This Workbook), Excel will check if there is a Sub procedure with the name Workbook_Open. Since there is in this workbook, the code will execute/run.
Save the workbook, close it and reopen it.
Enter a number from 1 to 7, click OK or press Enter and you will see the corresponding day in the first cell.
If you click Cancel or enter anything but a number
from 1 to 7 you will receive the message.
-----SIDEBAR-----
If you wanted run a macro/code every time you opened Excel, you would
save the workbook with the code in the folder C:\Documents and Settings\your
login name\Application Data\MicrosoftExcel\XLSTART. the
previous line is called a "path." to find it on your computer, you
would go to...
My Computer > Documents and Settings > to the folder with the name with which you login to your computer. If you computer doesn't ask you to login, you can try all of the few folders that are there. > (continue to) Application Data > Microsoft Excel > XLSTART.
After you save the file there, it will open in the background when you open a workbook, and since there is code there to run when it opens, the code will run for any workbook you open as long as Excel was not running on your computer when you opened it.
Although this is an Excel VBA example, it is also
possible to
have Word run code when a document is opened, whether the document is
new or previously saved.
-----SIDEBAR-----
In the following code there are a couple points of interest...
On Error Resume NextThe underscores and the space before each one are line continuation marks, telling VBA that it should consider these lines as one line. Without them we would receive a syntax error. If you count the commas between the opening parenthesis and the closing one, you will see that there are two, separating three arguments. Because, before the last underscore, we have finished typing the first argument, which is a string argument - the prompt for the InputBox, there is no ampersand before it.
Answer = InputBox("What day is it?" & vbCrLf & _
"1 = Sunday" & vbCrLf & _
"2 = Monday" & vbCrLf & _
"3 = Tuesday" & vbCrLf & _
"4 = Wednesday" & vbCrLf & _
"5 = Thursday" & vbCrLf & _
"6 = Friday" & vbCrLf & _
"7 = Saturday" & vbCrLf _
, "Excel VBA Example", 1)
On Error GoTo 0
After the first comma is, "Excel VBA Example," which is the Title argument that appears in the title bar of the InputBox dialog window. And the third argument is the default to display when the dialog opens. If the user presses OK without typing a number, it will be as if he had typed the number 1.
The letters vbCrLf are a VBA keyword that tells VBA that we want a Carriage Return and a Line Feed there. Carriage Return and Line Feed are typewriter terminology for what would be an Enter. In other words, Return the carriage and feed the paper to the next line, which is what the Enter key does on a computer.
And last, the line "On Error Resume Next" tells VBA that if there is an error in a following statement, Resume execution at the Next line, and "On Error GoTo 0" tells VBA to not Resume Next anymore, if the is an error show a message. We will learn about errors and how we handle them in another lesson. Let it suffice here that we needed these two lines to deal with the user pressing the Cancel button on the InputBox, which would give an error had we not told it to bypass any error.
And finally in our Excel VBA Example...
If Answer = "1" ThenThis is called an If..Then..Else block. In its simplest form it says...
Sentence = "Sunday"
ElseIf Answer = "2" Then
Sentence = "Monday"
ElseIf Answer = "3" Then
Sentence = "Tuesday"
ElseIf Answer = "4" Then
Sentence = "Wednesday"
ElseIf Answer = "5" Then
Sentence = "Thursday"
ElseIf Answer = "6" Then
Sentence = "Friday"
ElseIf Answer = "7" Then
Sentence = "Saturday"
Else
MsgBox "A number from 1 to 7 was not entered"
Exit Sub
End If
If something it true ThenThe "End If" line doesn't actually do anything during the code's execution. It's part of the syntax of the If..Then..Else block. It means, "This line is the End of the If block.
do this then go to the line "End If"
Else if the something wasn't true
do this
End If either way do this
This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage to learn more.
Return from an Excel VBA Example to our "Home Page"
