8. Problems with Pasting Text
Macros can have problems when pasting text from a cell or comment. In this lesson, I'll show you how to overcome this problem using 3 lines of code (but no more, I promise!).
Pasting text in a Macro
- When you paste text during a Macro recording, Visual Basic hard-codes the text into the Macro
- As a result, the output text from the Macro will never change
- To fix this problem, you'll need to use variables in your VB code
How to use variables
- Variables are simply names that can be assigned values in VB
- In this example, I assign the text in a comment to a variable
- The variable is then assigned to the text in the adjacent cell
ALT + L , P, M: Open Macro dialogue box
ALT + Q: Close Visual Basic Editor
In the previous lesson, we saw that our macro, designed to extract comments and place them in the adjacent cell, didn't work correctly.
Instead of taking the comment from the adjacent cell, for example InGen Corporation is very reliable, when we ran the macro, it took the comment from the first cell, which says Super customer. Let's jump back into our macro, and see if we can figure out what's going wrong. I'll open up the Macros tab, and Alt-E to edit the macro. In our macro, you can see that the hardcoded text from the first row, Super customer, is actually included as part of the macro. So of course, only this text will ever appear when we run the macro.
This problem with hardcoded text is a big limitation of recording macros. The macro doesn't know if you want to paste the same text every time, or if you want to paste the comment in each relative cell. To fix this problem, we're going to need to write some very simple code, employing a very important concept called a variable. A variable is a name that can be assigned a value, and then used in our macro. To declare a variable, I'm going to first create a couple of new rows, and then write Dim.
And the variable name will be cellComment.
Now I need to assign a value to this variable. To assign a value to a variable, we use equals. In programming, equals does not correspond to a logical test. It actually means let. So if I write cellComment is equal to ActiveCell.Comment.Text, this line means let the variable cellComment equal to the text of the comment of the active cell.
The dot that VBA uses between each of these words means belonging to, or of, when reading from right to left.
So the text belongs to the comment, that belongs to the active cell.
Once we have the comments text in our variable, we now need to output this cell comment into the adjacent cell. To do this, we move to the cell on the right, and let that cell's value equal to the cell comment.
So I'll create a new line, and to move to the cell on the right, I'll first select the active cell, and offset by a certain number of cells.
The offset function accepts two values. The first is the number of rows that we virtually want to move from the active cell. And in this case, it's zero.
And the next argument is the number of columns we want to move from the active cell. And in this case, that equals to one.
We'll then let the value of this cell equal to the cellComment variable.
And I'll delete the remaining lines.
I'll also make sure that my variable declaration aligns with the rest of the macro.
I'll then return to my Excel sheet, and test this on a new cell.
And we now have the correct comment appearing opposite Petrox Oil Company. We can try this again for Atlantic Northern, and for Klimpys.
And the macro keeps working correctly.
Now let's say you want to remove the comment from the original cell, before we paste into the Manager Comments column.
To do this, it's very simple. We return to our macro. So Alt-L-P-M, and Alt-E to edit.
And after we assign the variable, but before we add the text into the Manager Comments column, all we need to do is delete the comment from the active cell. And I can type ActiveCell.Comment.Delete.
If I now return to the Excel sheet, and run the macro for Three Waters, with Control-Shift-C, the comment now appears in the Manager Comments column, but is gone from the Three Waters cell.
You might be asking yourself at this stage, how could I create a macro that would loop through every row in a dataset, and only extract a comment if one existed? To do this, we'd need to write some additional VBA code, which I'll show you in my next course on the topic. With some basic macro knowledge now under your belt, you probably need to make a decision, as to whether you're going to learn VBA code or not. I'll try to help you make this decision in my next lesson.