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 am going to first create a couple of new rows and then write DIM. And the variable name will be cellComment. Now we need to assign a value to this variable. In programming, equals does not correspond to a logical test. It actually means LET.
So if I write cellComment is equal to ActiveCell dot Comment dot Text, this line means, let the variable cell comment equal to the text of the comment of the ActiveCell. The dot that VBA uses between each of these words means BELONGING TO or OFF when reading from right to left. So the Text belongs to the Comment that belongs to the ActiveCell. Once we have the comment's text in our variable, we now we 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 ActiveCell and Offset by a certain number of cells. The OFFSET function accepts two values. The first is the number of rows that we vertically want to move from the ActiveCell. And in this case it's zero. In the next argument, is a number of columns we want to move from the ActiveCell 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. So Ctrl + Shift + C, 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. Alt + E to edit and after we assigned the variable, but before we add the text into the Manager Comment column, all we need to do is delete the comment from the ActiveCell. And I can type ActiveCell dot Comment dot Delete. If I now return to the Excel sheet and run the macro for Three Waters with Ctrl + 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 on a data set and only extract comment if one existed. To do this, we 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 help you make this decision in my next lesson.