VBA Replace and Evaluate

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
161
Office Version
  1. 2019
Platform
  1. Windows
This is more an academic question. I am trying to understand how the 2 Replace functions work in the one line vba code. Now I feel I understand the one part of "REPLACE(#,5,0,"" 2 "")". The syntax looks exactly like the excel function Replace. I am also going to say that after that function is completed the result would be ABCD 2 EFGHI. I just would like confirmation that the place holder or # is the data in cells C1 to C3. This kind of leads me into the next question, how does the place holder get that data?

Ok so the next level Replace gets a little more confusing to me. It looks like the syntax of a VBA Replace.
string1
The string to replace a sequence of characters with another set of characters.
find
The string that will be searched for in string1.
replacement
It will replace find in string1.

If I assume the place holder or # represents the data in cells C1 to C3 than it kind of makes sense.

So in conclusion, I guess the question boils down to, how does the place holder or #, get the data?

VBA Code:
Sub test04()

    Range("C4:C6") = Evaluate(Replace("REPLACE(#,5,0,"" 2 "")", "#", "C1:C3"))
   
End Sub

20-09-23 Evaluate Len Replace ex 3.xlsm
C
1ABCDEFGHI
2ABCDEFGHI
3ABCDEFGHI
4ABCD 2 EFGHI
5ABCD 2 EFGHI
6ABCD 2 EFGHI
Eval and Replace 2
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,615
Office Version
  1. 365
Platform
  1. Windows
You've described everything fine, the 1st replace is the VBA version & replaces # with C1:C3 so you get an Xl formula of REPLACE(C1:C3,5,0,"" 2 "") & that is calculated by the evaluate method
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,580
They are not two Replace functions.

VBA Replace function simply returns a string with the requested replacement.
VBA Code:
strFormula = Replace("REPLACE(#,5,0,"" 2 "")", "#", "C1:C3")
' returns the following string 
' REPLACE(C1:C3,5,0," 2 ")

So it is basically an Excel formula:
Excel Formula:
REPLACE(C1:C3,5,0," 2 ")

So you have the following to calculate with the Evaluate function.
VBA Code:
resFormula = Evaluate(strFormula)

And it returns the result of the Excel function, which is the result of the function provided as a string. # doesn't get the data, you can use any string instead. It is just replaced with the C1:C3 string. The rest is done by the Evaluate function.
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
161
Office Version
  1. 2019
Platform
  1. Windows
Again, I am just trying to understand how this line of code works. You guys have given me another piece of the puzzle as I assemble this excel picture. This should be another tool in my excel tool box I can pull out in the future. So, thank you Fluff and smozgur for your input.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,580
Actually, I was thinking that I explained how it works step by step.
First, the VBA Replace function works and creates the string that will be evaluated as an Excel formula.
Then the Evaluate function calculates the given formula (or "name" in the general definition) and returns the result.
More info: Here, the Evaluate function does what the "=" (equal) sign does in a "Cell" object.

Does it still sound like a puzzle?
 

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
161
Office Version
  1. 2019
Platform
  1. Windows
Smozgur, I feel life is puzzle, so excel is like life. We just keep learning as much as we can and fitting the pieces together. Your explanation added a little more information I can use to understand this line of code. Now I am still digesting certain parts of this code but for the most part I feel I could use this code in the future. I also like these discussions as it just serves to educate us all in excel. Thanks for your time.
 

Forum statistics

Threads
1,147,566
Messages
5,741,864
Members
423,692
Latest member
Bhanu1988

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
Top