Question for Rick Rothstein about one of his macros

[AndyMachin]

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:

[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!

 

[Scott T]

You can use the @ and the user name to mention them. So to help @Rick Rothstein see this.

 

[Grummet]

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:

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:

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:

"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:

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:

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:

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!

-Grummet

 

[AndyMachin]

Thanks so much for your explanation Grummet! I really appreciate it.
I thought no one was interested in my question. Thanks! :D

 

[Rick Rothstein]

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.

 

[AndyMachin]

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!