Last Updated on January 11, 2023 by token
1. How to write first program in Excel VBA
If you already know what macros are and what the VBA is, it’s time to write your first program in VBA. First of all we would like to fill A1 cell in the Excel worksheet with message Welcome to VBA! To do this, open the VisualBasic Editor in the Developer’s tab or use Alt + F9 key combination. Then right-click on the Microsoft Excel Objects element. Then choose Insert and Module.
In the module’s code, write code FirstProgram(). When you write a program in module, called also procedure, it will always starts with a phrase Sub program_name(). Remember that brackets in the end of program name are important. Your programs will ends with the phrase End Sub. Between those phrases insert a single line code for addressing the A1 cell like in the example listed below.
'OfficeInside.Org Sub FirstProgram() Range("A1") = "Welcome to VBA!" End Sub
After writing your program, close the editor with the Alt + Q key combination. To run your program, go to the Developer tab. Then choose macros. From the macros select FirstProgram and run it.
As you can see with the Range command, you can specify the cell address in the worksheet in which your text will be inserted. If you want display message in the message window, you can use the MsgBox window. The example code looks like this:
'OfficeInside.Org Sub SecondProgram() MsgBox "Welcome to VBA!" End Sub
More about the MsgBox window you can read here: MsgBox. MsgBox Window will be use in next VBA Course topics.
2. Running the macro with a button
Ok, you already know how to write your Hello World program in VBA. Now try to run your program using the buttons in Excel. Go to the Developer’s tab. Next click the Insert icon. Then choose Button (form control) from the form controls.
After selecting it, you can directly assign a macro to your button. From the list of macros which will be displaying, choose one of your existing macros. For example FirstProgram(). Now, you can run macros by clicking on the button.
3. Debug.print command and Immediate Window i VB Editor
As you see, the user’s communication with VBA can by done in many ways. Another way to run the program in VBA Excel is to use the Immediate Window which is quick launch window in VBA. To enable it in the VisualBasic editor, go to the View tab and click Immediate Window. The Immediate window should appear under the Code window. You can use the window in several ways. To display the message in the window, you need to use the Debug.print command according to the following syntax. The command is entered in the Code window.
'OfficeInside.Org Sub ThirdProgram() Debug.Print "Welcome to VBA!" End Sub
You can also enter VBA commands directly in the immediate window. It will run using the Enter key. In this way, you can check very quickly, how a sample of your code works.
4. Useful Exercises (You can enter solution in a comment)
4.1. Write a program named MyProgram that fill cells A1, A2 and A3 with Your Name, Last Name and Year of Birth.
4.2. Update the above task using the MsgBox window. Call the window using the button.
4.3. Do the same exercise using Debug.print command.
More examples of Excel VBA basics can be found in the Excel VBA Tutorial section and Excel VBA How To section. If you have any questions about this chapter, you can post them on the forum without logging in.
File with examples listed in this article is attached below: