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

ramohse

Board Regular
Joined
Sep 30, 2013
Messages
50
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Cross-posted here.

Do you have any routines or modules called INDIRECT? Is it only the INDIRECT worksheet function that causes the problem?
 
Upvote 0
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
My impression is that ramohse is trying to input INDIRECT() into a cell, not use it in VBA.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top