VBA Evaluate function with string arguments

excel vba evaluate
Mats Olsson picture Mats Olsson · Feb 27, 2015 · Viewed 13.3k times · Source

I have this function working ( It returns the row where the text DK001 sits in the ID range)

Found = Application.Evaluate("=IF(ID=""DK001"",ROW(ID),""x"")")

I would like to feed the searchcriteria (e.g. DK001) as a string, like

Found = Application.Evaluate("=IF(ID=SearchString,ROW(ID),""x"")")

I fail in creating a string that is accepted as a search criteria. I need your help on this! What am I doing wrong?

This Evaluate function is haunting me ....

What if I now wanted to send a value (not a string) to the function?

Found = Application.Evaluate("=IF(ID=1,ROW(ID),""x"")")

The above works!

But if I want this to be a variable like

Found = Application.Evaluate("=IF(ID=MyValue,ROW(ID),""x"")")

What then?

Answer

Alex K. picture Alex K. · Feb 27, 2015

Double " to include them as literals:

SearchString = "DK001"
Found = Application.Evaluate(""=IF(ID=""" & SearchString & """,ROW(ID),""x"")")

Related questions

Does .Value = .Value act similar to Evaluate() function in VBA?

Consider the following snippet. It writes the same formula to two cells A1 and A2 Sub Main() With Range("A1") .Formula = "=1+1" End With With Range("A2") .Formula = "=1+1" .Value = .Value End With End Sub The second with block uses .Value = .Value …

Read More
Executing build-in functions from VBA (Excel)

I need to have a capability to execute any kind of build-in functions (such as 'sum' or 'len') from VBA (MS Excel). One of the restrictions that I have is that I may not pass cell ranges as arguments to …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More