How to evaluate an workbook object by its name in code?

Declaration:
Private wbAcctFees As Workbook

Please Login or Register  to view this content.

Formula in a Cell:
=VLOOKUP(A2,INDIRECT("["&GetFileNamefromCode("wbAcctFees")&".xlsx]gen_rpt'!$A:$K"),11,)

Please Login or Register  to view this content.

Why its not working? and Whats the solution for this? Thanks in advance.

Note:
The workbook having the cell with the formula is having reference to the workbook having the variable wbAcctFees.

Haven't got the answer over here:
http://www.mrexcel.com/forum/excel-q...name-code.html

shouldn't you have a identifier for the workbook and/or sheet you're trying to open?

Please Login or Register  to view this content.

Originally Posted by 6StringJazzer

The OP wants to be able to pass in a variable name as a string (not the variable or its value) and then have the code take that variable name and then determine the value of that variable dynamically. However, that is not how Evaluate works, and there is not another built-in way to do this. A different approach is needed. There is further discussion at the cross-post linked above.

Evaluate does things like if you use the expression

Evaluate("A1")

it will return a Range object for the range A1.

Yes StringJazzy. That's exactly what I want to do? To change a single string into a code. No single line of code to do that? Let's forget the evaluate. I don't want to go with it as it wont change a string to any object other than the excel natives.

there is not another built-in way to do this. A different approach is needed.

Is learning the Dictionaries and Collections the only to achieve this? to change a string into a code

There is further discussion at the cross-post linked above.

Can someone please expalin how to achieve this by using defined names?

When I see a question like this, asking for something very specific that is not supported by the language, I refocus on the overall problem you want to solve. I need we need to rethink the whole solution.

You have a formula in a worksheet that refers to a workbook that was opened by VBA code. Do you have just one variable that refers to one workbook and one formula that needs it? If so then you don't even have to pass a variable to the code, you can just return the name of the workbook that was opened in Function Mac (by the way, I would make that Sub Mac because it doesn't return a value--a matter of programming style).

The best way to get a solution for this is to describe your whole problem. Attaching a workbook would be even better.