Donald wrote to ask for help writing an Excel VBA program that copied one row of an Excel Worksheet to another. He also had a something to say about Microsoft and the way they have ruined VB. (See the guest blog here.)
Donald had some warnings for me too. In a nice way, he said I made things too complicated as well. So this article is written to be as non-technical as possible.
-> Specific directions for starting the Excel developer environment, and the special security requirements for VBA macros are not included to avoid complicating the issue with something Donald did not ask for.
-> More attention is given to the design decisions I made while coding this simple example.
-> There are lots of other ways this could be coded and this is just one. If you have a better one, let us know!
What Donald wanted is an example of the kind of thing that Excel VBA can be really useful for. Donald wrote that he wanted, "Evolving Accounting versus Summary Accounting. To explain: I wish to have one file of all my receipts, with date, account, category, provider, product/service, and cost entered one line at a time, as they occur."
That's reasonable. So I put together a simple Excel VBA program to copy a row from one worksheet to another. I used three columns in this example (rather than all the fields Donald mentioned) for simplicity:
-> An alpha column for text
-> A numeric column - an automatic sum is created on the target worksheet
-> A date column - the current date and time is filled in automatically
The first decision to make is how to trigger an event that will copy the row. I went with the standard, a Button form control. In Excel for Office 2010, click Insert on the Developer tab. Then click the Button form control and draw the button where you want it. Excel automatically displays a dialog to give you a chance to select a macro triggered by the Click event of the button, or create a new one.
The second decision is to decide how to find the last row in the target worksheet so the program can copy a row at the bottom. There are at least three ways you might do this:
-> Code a loop that will test each row to determine if it has been used.
-> Use the the Excel SpecialCells(xlCellTypeLastCell).Select method.
(I used a similar technique to sum the numeric column.)
-> Maintain the number of the last row in the worksheet. This is the way I did it.
To maintain the number of the last row, you have to store that number somewhere. This might be a problem because the user might change, or even delete the number. To get around this problem, I simply placed it in the cell directly underneath the form button. That way, it's inaccessible to the user. (The easiest thing to do is enter a value in the cell and then move the button over it.)
Click Here to display the illustration
To figure out the actual VBA code, I did several Record Macro operations just to see how Excel would do things and then copied the recorded code. Here's what I eventually settled on:
Sub Add_The_Line() Dim currentRow As Integer Sheets("Sheet1").Select currentRow = Range("C2").Value Rows(7).Select Selection.Copy Sheets("Sheet2").Select Rows(currentRow).Select ActiveSheet.Paste Dim theDate As Date theDate = Now() Cells(currentRow, 4).Value = CStr(theDate) Cells(currentRow + 1, 3).Activate Dim rTotalCell As Range Set rTotalCell = _ Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) rTotalCell = WorksheetFunction.Sum _ (Range("C7", rTotalCell.Offset(-1, 0))) Sheets("Sheet1").Range("C2").Value = currentRow + 1 End Sub
The code that sums the numeric column might not be obvious. If you search for other Excel tips on this, you'll see most pages say you should use ...
... rather than ...
C65536 is a "magic number". It was the maximum number of rows in a worksheet in previous versions but Microsoft has now changed that to 1048576. The previous "magic number" still works in nearly all cases, but it's better coding practice to use Microsoft's enumerations.
xlUp is actually another "magic number" or more technically, an enumerated constant. If you check it out in Object Browser, you'll see that it's actually the number -4162. But that doesn't mean anything except that the End method recognizes it. (You can actually substitute -4162 for xlUp and it works the same way.) And Offset(1, 0) simply moves up one row in the same column, so the net effect is to select the last cell in column C.
In words, the statement says:
Go to the last cell in column C (equivalent to End+Down Arrow)
Then go back up to the last unused cell (equivalent to End+Up Arrow)
The go up one more cell
Finally, the last statement updates the location of the last row. Here's Sheet2 after it has been updated with a few rows.
Click Here to display the illustration
In my blog, I wrote that VBA is probably harder than VB.NET because you have to know both VB and Excel VBA objects. The "magic numbers" above are good examples of the kind of specialized knowledge that is critical in being able to write VBA macros without looking up three different things for every statement you code. In addition, Microsoft has made great progress in upgrading the Visual Studio editor to help you figure out the correct syntax but the VBA editor is still about the same as it was at least a decade ago. Hopefully, Microsoft will get their act together someday and provide an up-to-date programming environment for VBA someday. Hey! "Wishing" is free.