Join Curt Frye for an in-depth discussion in this video Writing a value to a cell, part of Up and Running with VBA in Excel.
There are two main ways to show the user the results of a VBA procedure: displaying the result in a message box or writing the result to a cell range. I'll cover message boxes in another movie, but I'll show you how to write values to a cell range now. First, just give you an idea of what is going on in our worksheet here, I have a set of Sales data from here, and then I also have a cross table where I have the number of sets that an individual orders, the number of bottles in each one of those Sets. In this grid we'll display the total number of bottles in a set of varying sizes.
Now, let's take a look at the code that I'm using. If I just want to write a value to a cell, then I can use the Range Object's Value Property. So in this case, I have the strAddress = D6, and if I have the Range D6, I can assign the Value property, the string Commission. Now, before I run that, let's flip back here, pressing Alt+F11. You'll notice that I have an Excel table, with a cell in C6, that means if I add a cell to D6, it will be included in the table.
If you click here and type in data, Excel will include it in the table. So, let me show you what that looks like when I do it using my code. Inside the code module, press F5 and Alt+F11 to come back, and you can see that Excel added a Commission column to the table. Flip back to the code. Now, you notice that I used a variable to assign a value to the Range.Value property, and I also use a variable to assign the Address. Even though you can use a variable as the Range, you can't perform a calculation in the reference.
In other words, there's no way to write a version of Range D6 that performs the calculation Range D6 plus one row. Yes, you can use the Offset property, but that takes time, and it could be a bit clunky; instead, you can use the Cells. Value Property to write and read values. I do that here in this Cells command. The Cells command has two arguments: The first is the Row, and the second is the Column. Now remember that order is the opposite of what you do here when you're typing out an Address literally, for example, D6; in that case it's Column first and then Row, but for Cells, it's Row first and then Columns.
In the subroutine, to calculate the Row Number, I find the Row Number of the current Cell, that's the ActiveCell's Row property, and add it to this integer here, which is in the Counter. Say, for example, the ActiveCell were in Row Number 1 and I were counting here 1 through 7, the first time through the For Next loop, then the RowNo would be the RowNo of the ActiveCell, which is 1, plus 1, which is the value of the RowCounter variable at this point, so it would be RowNo 2. And the code would do the same thing for the Column; it would find the Column Number of the ActiveCell and add to that, the value in the Count variable here, the integer ColumnCounter.
When I run this code, it's going to create an array of values, and I have chosen, as my starting cell, Cell F5. So that means that the first value will go one cell to the right and one cell below F5. So in other words, it should end up in G6. So when I press F5 and press F11 to move back, you see that indeed F5 is the ActiveCell, and the Excel VBA code filled in the values in the interior of my grid. Writing values into an Excel Worksheet is fairly straightforward, but you should be on the lookout for opportunities to use the Cell's Objects Value Property, instead of more complex methods using other properties.
- Working in the Visual Basic Editor
- Adding code to a macro
- Creating, exporting, and deleting code modules
- Declaring and using variables
- Managing variable scope
- Defining arrays
- Managing workbooks and worksheets with VBA
- Repeating tasks with loops
- Debugging VBA code
- Cutting, copying, and pasting cell data
- Running and triggering event procedures