INDIRECT() not working:"Can't Find Object or Library" error

[ramohse]

Hello all,



Firstly, thank you for your time.

I am attempting to create a dynamic dashboard that will allow the user to select a business unit from a drop-down list, then that selection will populate a table with information from the appropriate tab. I am trying to use INDIRECT () to accomplish this, but any time I input any INDIRECT() function in any sheet in the file I receive the error message, "Can't Find Object or Library."


I am bothered because this is a standard Excel function. I am using some VBA in this model, but no UDF's. What could be causing this, and how might I be able to resolve it? I appreciate any assistance you can provide.

 

[RoryA]

Cross-posted here.

Do you have any routines or modules called INDIRECT? Is it only the INDIRECT worksheet function that causes the problem?

 

[Jonmo1]

Indirect is not available to the VBA application.

But since you're using VBA, there's no need for indirect to begin with.
You can easily build ranges with text strings directly in VBA.

Can you post the Indirect formula you attempted, and we can work it out with standard VBA coding.



As a last resort, you can always use Evaluate with a string representing the indirect function as it would appear in a Cell.

MyVariable = Evaluate("=INDIRECT(A1)")
Where A1 contains a text string cell reference.

But that would be written just as easily without indirect like this
MyVariable =Range(Range("A1").Value)

 

[ramohse]

I do not have any other routines or modules called "Indirect." I have a basic loop that searches and replaces items across several tabs that runs from a toggle button, and another that reformats the dashboard based on selecting "(none)" as a third input from a drop-down list.

I have not had any other issues with any other functions used in the file. I have used SUM, SUMIF, VLOOKUP, AVERAGE, AVERAGEIF, MID, LEN, RIGHT, LEFT, and a few other basic ones.

Sorry about the cross-post. I didn't realize these were sister sites.

 

[RoryA]

Sorry about the cross-post. I didn't realize these were sister sites.

They're not but both (like most forums) have rules about cross-posting on other sites. Our rule 10:

Rule #10:
We prefer that members do not cross-post questions to other forums, but when this does occur members must make clear that they have cross-posted and must provide links to the cross-posts. Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: Excelguru Help Site - A message to forum cross posters

 

[Jonmo1]

I have not had any other issues with any other functions used in the file. I have used SUM, SUMIF, VLOOKUP, AVERAGE, AVERAGEIF, MID, LEN, RIGHT, LEFT, and a few other basic ones.

Not ALL worksheet functions are available to be used in VBA (Indirect is one of them).
Because VBA itself has it's own methods of accomplishing those tasks.

 

[RoryA]

Does it matter where you input the function, or does it simply not work anywhere in the workbook? Is it only that one workbook you have issues with?

 

[ramohse]

Jonmo1 - I could do what I was trying to do with VBA, but I read somewhere to use in-Excel functionality as often as possible because it cuts down on process time/resources/etc... is it not possible to use INDIRECT and VBA at the same time? I am relatively new to VBA.

 

[Jonmo1]

I think you may have misunderstood that 'rule of thumb' to use Excel functionality as often as possible..

It means to use Excel functionality (within Excel, not in VBA)
In other words, avoid using VBA if the same task can be accomplished with standard cell formulas, or pivot tables, or any other function that is built into Excel.

VBA is Not really part of Excel.
VBA is it's own program that is kind of 'bundled' with Excel (or with MS. Office actually).


But once you've made the choice to use VBA, then use VBA native functions.
Avoid using Worksheetfunctions in VBA if you can.

 

[RoryA]

My impression is that ramohse is trying to input INDIRECT() into a cell, not use it in VBA.