VBA Replace and Evaluate

[Ezguy4u]

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?

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
1	ABCDEFGHI
2	ABCDEFGHI
3	ABCDEFGHI
4	ABCD 2 EFGHI
5	ABCD 2 EFGHI
6	ABCD 2 EFGHI
Eval and Replace 2

 

[Fluff]

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]

They are not two Replace functions.

VBA Replace function simply returns a string with the requested replacement.

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:

REPLACE(C1:C3,5,0," 2 ")

So you have the following to calculate with the Evaluate function.

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]

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]

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]

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.