VBA Function to evaluate hlookup text string as formula

[Eric]

I have a simple function using Evaluate to evaluate a text formula. It
works just fine, even if it references other spreadsheets on the
network (the formula has full path). What I can't get it to do
properly is have it evaluate an Hlookup referencing another
spreadsheet on the network if I have the function look to another
cell's text. BUT, if I put the hlookup inside the function, it
evaluates just fine.

Function I added in VBA:
Public Function myFunct(InputString As String)
myFunct = Application.Evaluate("=" & InputString)
End Function

----------------------------
Formula in A14:
=PERSONAL.XLS!myfunct(B14)
Evaluates to:
#REF!

Text in B14:
HLOOKUP(L17,'C:\Documents and Settings\eric\My
Documents\[Book1.xls]Sheet1'!$E$25:$F$25,1,FALSE)
---------------------

Formula in A15:
=PERSONAL.XLS!myfunct(HLOOKUP(L17,'C:\Documents and Settings\eric\My
Documents\[Book1.xls]Sheet1'!$E$25:$F$25,1,FALSE))
Evaluates properly.

----------------------
Formula in A14:
=PERSONAL.XLS!myfunct(B14)
Evaluates to:
Evaluates properly.

Text in B14:
HLOOKUP(B8,8:8,1,FALSE)

So, it looks like it's something to do w/ the way the function
evaluates the text in a cell, versus just passing the text itself into
the function. Why???

I want to make dynamic text+formula strings that extensively use
hlookup, but if I can't get this to work, it'll be manual updates! Any
clues why this one thing doesn't work when it evaluates just fine for
other

Thanks! ER

 

[Tom Ogilvy]

Evaluate won't work with a closed workbook, so that is why your A14 is
failing.

In A15, Excel evaluates the argument to myfunct before passing it to
myfunct, so it gets the results from the look up and passes it as a string
to Myfunct which does nothing to it and returns it.

change

Public Function myFunct(InputString As String)
myFunct = Application.Evaluate("=" & InputString)
End Function

to

Public Function myFunct(InputString As String)
myFunct = InputString
End Function

and you will see the difference.

--
Regards,
Tom Ogilvy

I have a simple function using Evaluate to evaluate a text formula. It
works just fine, even if it references other spreadsheets on the
network (the formula has full path). What I can't get it to do
properly is have it evaluate an Hlookup referencing another
spreadsheet on the network if I have the function look to another
cell's text. BUT, if I put the hlookup inside the function, it
evaluates just fine.

Function I added in VBA:
Public Function myFunct(InputString As String)
myFunct = Application.Evaluate("=" & InputString)
End Function

----------------------------
Formula in A14:
=PERSONAL.XLS!myfunct(B14)
Evaluates to:
#REF!

Text in B14:
HLOOKUP(L17,'C:\Documents and Settings\eric\My
Documents\[Book1.xls]Sheet1'!$E$25:$F$25,1,FALSE)
---------------------

Formula in A15:
=PERSONAL.XLS!myfunct(HLOOKUP(L17,'C:\Documents and Settings\eric\My
Documents\[Book1.xls]Sheet1'!$E$25:$F$25,1,FALSE))
Evaluates properly.

----------------------
Formula in A14:
=PERSONAL.XLS!myfunct(B14)
Evaluates to:
Evaluates properly.

Text in B14:
HLOOKUP(B8,8:8,1,FALSE)

So, it looks like it's something to do w/ the way the function
evaluates the text in a cell, versus just passing the text itself into
the function. Why???

I want to make dynamic text+formula strings that extensively use
hlookup, but if I can't get this to work, it'll be manual updates! Any
clues why this one thing doesn't work when it evaluates just fine for
other

Thanks! ER

 

[Eric]

Tom,
I made the change, but it now displays the text I pass to it.

Maybe I should just take a step back and ask if there an easier way to
put together an hlookup based upon concatenating dynamic text strings
that refer to an array in another closed spreadsheet. I've tried using
Indirect, but it has the same problem.

Thanks!
Eric