![]() Let’s look at the code name and what it is. We do this by changing the code name of the worksheet. It is considered good practice to give these sheets more meaningful names. In the activities so far, we have been using the default names of the worksheet such as Sheet1 and Sheet2. Copying between cells is a fundamental part of Excel VBA, so understanding this will really help you on your path to VBA mastery. ' copies the value from B1 on sheet2 to A3 on sheet1 Here are some more examples: ' Sub CopyValues() Using a line like this we can copy a value from one cell to another. When we have the reference to a worksheet we can use the Range property of the worksheet to write to a range of one or more cells. We will look at this in the section The code name of the worksheet. We can only use the code name to reference worksheets in the workbook containing the code. In this code, Sheet1 refers to the code name of the worksheet. Let’s look the part of the code to the left of the equals sign Sheet1.Range( "A1") = 5 The line is saying “the left cell\variable\range will now be equal to the result of the item on the right”. VBA evaluates the right of the equals sign and places the result in the variable/cell/range that is to the left of the equals. copy) values between cells and variables. We use lines of code like these to assign(.i.e. However in most cases we don’t need to use Value as this is the default property. We can also write this line like this Sheet1.Range( "A1").Value = 5 Let’s look at the line of code we used in the previous section of this VBA Tutorial Sheet1.Range( "A1") = 5 You should see “Some text” in cells B1, 5.55 in the cells C3 to E5 and the current time and date in the cell F1. Add each of the following lines to your sub, run the sub and check the results.Note: If you don’t place the cursor in the sub, VBA will display a list of available subs to run. Select Run->Run Sub/Userform from the menu(or press F5). Click in the sub to ensure the cursor is placed there.You have created a sub! Let’s take it for a test drive. We place our code between these two lines. VBA will automatically add the second line End Sub.Enter the following line in the code window and press enter.Make sure the name is visible in the title bar. Select the module by double-clicking on it in the Project – VBAProject window.Take the module you created in the last activity or create a new one.The screenshot below shows the main parts of the Visual Basic Editor: If the Visual Basic editor is not currently open then pressing Alt F11 will automatically open it. Pressing Alt F11 switches between Excel and the Visual Basic Editor. ![]() VBA Editor: This is where we write our code. There is no limit(within reason) to the number of modules in a workbook or the number of subs in a module. A module contains subs which in turn contain lines of code. Module: A module is simply a container for our subs. A macro and a sub are essentially the same thing. When we “Run” the sub, VBA goes through all the lines of code and carries out the appropriate actions. Sub: A sub is made up of one or more lines of code. Generally speaking, they perform one task. It is short for Visual Basic for Applications. VBA: VBA is the programming language we use to create macros.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |