Testing the VBA procedure step by step
Testing is the most time-consuming part of any VBA project. During the development of a project you will use 20% of your time analysing and designing, 15% programming and 65% testing.
During the testing phase, you will correct bugs, typos and the logical errors. More importantly you will improve your original project, fine tune it, discover better ways to do things and add code.
You have created your first macro and tested it using the "Run" button. You can also test a macro step by step.
Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in topic.
(i) Go to Excel and make sure that cells A1, A2 and A3 of Sheet1 are empty.
(ii) In VBE go to the Code window of Sheet1 and copy/paste the following macro:
Sub PreTest()
Range("A1").Value = 09
Range("A2").Value = 22
Range("A3").Formula = "=A1+A2"
Range("A1").Select
End Sub
(iii) Click anywhere within the macro and then press the F8 key at the top of your keyboard. VBE highlights the first line of code in yellow.
(iv) Right-click on the small yellow arrow and see a menu appear
(v) Press on "F8" a second time. No line has been executed yet and if you go to Excel you will see that cells A1 to A3 are still empty. The next time you press "F8" , VBE will execute the yellow-highlighted line.
(vi) Press "F8" a third time. The yellow-highlighted line is now "Range("A2").Value = 66". VBE has executed the previous line "Range("A1").Value = 34" has been executed so if you go to Excel (ALT/F11) you will see 32 in cell A1.
(vii) Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see what happened in cell A2.
(viii) Come back to VBE (ALT/F11) and press "F8" again. Go to Excel and see that there is a formula in cell A3.
(ix) Come back to the VBE (ALT/F11) and press "F8" again, cell A1 is now selected in Excel.
(x) Press "F8" again. Nothing happens in Excel but "End Sub" is highlighted in yellow
(xi) Press "F8" again. Nothing happens in Excel no more lines in VBE are highlighted in yellow.
The macro hac been tested, the test is over.
In the code change the addresses A1, A2 and A3 respectively to B1, B2 and B3. Test the macro again. Do it as many times as you want.
You have tested a macro step by step. In the downloadable tutorial you will learn how to test parts of a macro, how to come back a few lines, make changes and re-execute these lines. You will also discover this most important functionality, how to share the screen between Excel and VBE.
Wednesday, December 8, 2010
The Code Window in the Visual Basic Editor of Excel
The Code Window is where 90% of the VBA work is done; writing VBA sentences, testing your VBA procedures (macros) and modifying them when needed.
To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook.
There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro.
To illustrate everything that you can do in the Code window we will start by creating a small macro in an empty workbook.
There are plenty of other operations that you can execute in the code window. For example, you can test a macro line by line (step by step), go back a few lines and make corrections, use breakpoints to test only part of a macro.
The Properties Window in the Visual Basic Editor of Excel
The Properties window shows you the properties of the component that is selected in the Project Window (single click). For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window.
As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1). You will discover later the advantages and disadvantages of changing this property. The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.
Changing the "Name" Property. You will also learn how to name your modules and work with a few other properties of the objects appearing in the Project Window.
Close the VBE and close Excel without saving anything.
As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1). You will discover later the advantages and disadvantages of changing this property. The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.
Changing the "Name" Property. You will also learn how to name your modules and work with a few other properties of the objects appearing in the Project Window.
Close the VBE and close Excel without saving anything.
The Project Window in the Visual Basic Editor of Excel
As you can see, the Project window shows you all the workbooks that are open ("Book1") in the example below) and their components. You can use the + and - signs to show the details.
A new Excel workbook includes three sheets and another component named "ThisWorkbook" is a component in which you will store the macros ( also called VBA procedures) that should start automatically when the workbook is opened.
We will now complete a brief exercise to learn how easy it is to work within the Project Window.
A new Excel workbook includes three sheets and another component named "ThisWorkbook" is a component in which you will store the macros ( also called VBA procedures) that should start automatically when the workbook is opened.
We will now complete a brief exercise to learn how easy it is to work within the Project Window.
The Visual Basic Editor in Excel (VBE)
When you want somebody to do some work for you, you open your Email program and you send him a message in a language that he understands (English, Spanish, French, etc). When you want Excel to do some work for you, you open the Visual Basic Editor and you write the instructions in a language that Excel understands VBA (Visual Basic for Application).
You will develop, test and modify VBA procedures (macros) in the Excel Visual Basic Editor (VBE). It is a very user-friendly development environment. VBA procedures developed in the VBE become part of the workbook in which they are developed and when the workbook is saved the VBA components (including macros, modules, userforms and other components) are saved at the same time. So, when you send the workbook to the "Recycling Bin" the VBA procedures (macros) are destroyed.
There are no risks to your computer or to Excel in completing the task below. At any time if you feel uncomfortable, just close Excel without saving the workbook and try again later.
For users of Excel 1997 to 2010: The first thing that you need to do is to make sure that the security level of Excel is set at either "Low" or "Medium" so that you can use the macros (VBA procedures) that you develop. From the menu bar of Excel select "Tools" then "Macro" then "Security" and select "Medium".
For users of Excel 2007 to 2010: From the "Developer" ribbon click on the "Macro Security" button. Check the second level "Disable all Macros with Notification" and you are set.
Setting up the Visual Basic Editor in Excel (VBE)
The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient.
Print this page, open Excel and open a new workbook (Book1).
On your keyboard press the "ALT" key (left of the space bar) and hold, strike the "F11"key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press "ALT/F11" and you are back into Excel. Use the "ALT/F11" key to go from Excel to the VBA and back.
Close the VBE and close Excel without saving anything.
You will develop, test and modify VBA procedures (macros) in the Excel Visual Basic Editor (VBE). It is a very user-friendly development environment. VBA procedures developed in the VBE become part of the workbook in which they are developed and when the workbook is saved the VBA components (including macros, modules, userforms and other components) are saved at the same time. So, when you send the workbook to the "Recycling Bin" the VBA procedures (macros) are destroyed.
There are no risks to your computer or to Excel in completing the task below. At any time if you feel uncomfortable, just close Excel without saving the workbook and try again later.
For users of Excel 1997 to 2010: The first thing that you need to do is to make sure that the security level of Excel is set at either "Low" or "Medium" so that you can use the macros (VBA procedures) that you develop. From the menu bar of Excel select "Tools" then "Macro" then "Security" and select "Medium".
For users of Excel 2007 to 2010: From the "Developer" ribbon click on the "Macro Security" button. Check the second level "Disable all Macros with Notification" and you are set.
Setting up the Visual Basic Editor in Excel (VBE)
The Visual Basic Editor is a program within Excel that allows you to communicate with Excel. We will open it and start by setting it up so that working within it becomes easy and efficient.
Print this page, open Excel and open a new workbook (Book1).
On your keyboard press the "ALT" key (left of the space bar) and hold, strike the "F11"key (the F11 key is at the top of your keyboard).You now see the Visual Basic Editor. Again press "ALT/F11" and you are back into Excel. Use the "ALT/F11" key to go from Excel to the VBA and back.
Close the VBE and close Excel without saving anything.
Subscribe to:
Posts (Atom)