Question for Rick Rothstein about one of his macros

AndyMachin

New Member
Joined
May 30, 2018
Messages
29
Hello everyone specially Rick,
Last year you posted a macro, and I still trying to understand how it works. I know It is super powerful using Evaluate, but there is not so much info about how to use @ as you did in the macro. Can you please make a breakdown of your code:

Code:
[COLOR=#333333][FONT=monospace]Sub PrefixAnApostophe()
With Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
   .Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", .Address))
End With
End Sub[/FONT][/COLOR]

I really want to understand this macro
Thanks!
 
Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Andy, your question intrigued me, so I took a look at this and here's how I understand it; The use of the @'s are inconsequential, as it's simply the character Rick chose to use in the Replace function.

Preface: For those unfamiliar with the original question, here is the post.

To help explain, I'm going to modify the original code for use on a single cell:
Code:
ActiveCell.Value = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", ActiveCell.Address))

The first part of the code to focus on is the IF statement. In the VBA code the IF statement is a literal string:

Code:
ActiveCell.Value = Evaluate(Replace([COLOR=#ff0000]"IF(@="""","""",""'""&@)"[/COLOR], "@", ActiveCell.Address))

This literal string is nested in the VBA Replace function. Assuming the active cell is Q2, the Replace function replaces the @'s and changes the literal IF string into:

Code:
"IF(Q2="""","""",""'""&Q2)"

Technically .Address without any additional parameters would return the absolute reference, $Q$2, but I'm leaving those out to make it easier to read.

If this was used as a formula on the worksheet it would look like this:

Code:
IF(Q2="","","'"&Q2)

In other words, If Q2 is blank then return a blank value, otherwise return a single quote ' and the value of Q2.

So back to VBA, after the Replace function has executed and created the new literal string the code has now changed to:

Code:
Evaluate("IF(Q2="""","""",""'""&Q2)")

Rick's original code is looking at the cell to be changed, creating an IF formula based off of that cell, Evaluating it in the VBA code, and then overwriting the value of the cell with the end result. The end result being a blank "" value or the cells original value preceded by a single quote mark.

Another (inferior) way to write this would be something like this:
Code:
For Each cell In Range("Q2", Cells(Rows.Count, "Q").End(xlUp))
    If cell.Value = "" Then
        'Do nothing
    Else
        cell.Value = "'" & cell.Value
    End If
Next

However, running it this way is much slower than Rick's code. My guess is because it has to cycle through the code for each cell, which was part of the OP's problem. I thought Evaluate may have been the key to Rick's code, but I think it's the vehicle needed in order to use the With method, and the combination of the two is why it works so well.

I still consider myself a novice, and I rarely use With statements or Evaluate, so I can't say for sure why his code is more optimized than a traditional loop. I will however try this With & Evaluate trick in the future! :biggrin:

-Grummet
 
Last edited:
Upvote 0
Thanks so much for your explanation Grummet! I really appreciate it.
I thought no one was interested in my question. Thanks! :D
 
Upvote 0
Thanks so much for your explanation Grummet! I really appreciate it.
I thought no one was interested in my question. Thanks! :D
Sorry I missed your question originally (I've been doing a lot of travelling of late and my online time has been much less recently, so I do not get to see all the new questions that get asked). I am glad Grummet step in to bring your question to the top of the list so I could see it. As Grummet said, any character would do in place of the @ symbol so long as that character is not natively in the formula being constructed. What the Evaluate function does is take an Excel formula passed into it as a text string and evaluates it in the same way it would be evaluated in a worksheet cell. Using a stand-in character for a repeated address or row number makes constructing the formula much easier than if you had to concatenate the values into to the text string multiple times (you simply use the stand-in character and then use the Replace function to replace is afterwards. I usually use the @ symbol for addresses and the # symbol for numbers. One huge feature of the Evaluate function is it returns arrays of values when arrays are being evaluated (sometimes you have to so some extra steps to force it, but generally that is what is going on). The beauty is that VBA can operate on these array of values quite quickly as everything is being done in memory.
 
Upvote 0
Sorry I missed your question originally (I've been doing a lot of travelling of late and my online time has been much less recently, so I do not get to see all the new questions that get asked). I am glad Grummet step in to bring your question to the top of the list so I could see it. As Grummet said, any character would do in place of the @ symbol so long as that character is not natively in the formula being constructed. What the Evaluate function does is take an Excel formula passed into it as a text string and evaluates it in the same way it would be evaluated in a worksheet cell. Using a stand-in character for a repeated address or row number makes constructing the formula much easier than if you had to concatenate the values into to the text string multiple times (you simply use the stand-in character and then use the Replace function to replace is afterwards. I usually use the @ symbol for addresses and the # symbol for numbers. One huge feature of the Evaluate function is it returns arrays of values when arrays are being evaluated (sometimes you have to so some extra steps to force it, but generally that is what is going on). The beauty is that VBA can operate on these array of values quite quickly as everything is being done in memory.

No problem Rick ;)
Thanks to both of you!
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top