• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 981
  • Last Modified:

Excel VBA - Error if range doesn't include worksheet name

On my machine, I don't get an error, but on other machines, I get the following error:

"Compile Error:  Can't find project or Library"

This error is triggered by range names that don't include the sheetname AND use brackets[ ]
There is no error if range references use the regular old    Range("rName")   syntax.

Brackets [ ] syntax works OK if the worksheet name precedes the brackets, but this totally defeats the purpose of using brackets, which is to shorten the code.

[a1].select      <-error
Sheets("MySheet").[a1].select    <-OK

[rName].Select   <-error
Sheets("MySheet").[rName].Select   <--OK
Range("rNamedRange"').Select   <--OK

So.... how do I make it so that my code with range names in brackets will run in all machines without having to type out the sheetname for each rangename reference?

Thanks, --Andres
1 Solution
I never use brackets for range names - not to say its good or bad, I just don't use them.

May I suggest the following, which achieves the purpose of shortening the code:

sub example()
dim wkb as workbook
dim sht as worksheet
dim r as range

   set wkb = ThisWorkbook
   set sht = wkb.Sheets("MySheet")

   set r = sht.Range("A5")

   r.Select '<-will work correctly


  sht.Range("A5").select '<- will work correctly.


HOWEVER, your use of:


is not incorrect.

BUT the named range "range" must be on the active sheet.

You can, however, execute some methods on [range] while not in the active sheet.

Here's an example of a lot that will work, and one example that won't.  Because of this (and, I guess, my habits), I never use the brackets - instead, I create shortcuts by assigning down to the range variable, itself, which contains the parent sheet and parent/parent workbook inherent in the object.

That's why r.select, or other methods with r (see setup, above) work...

Sub test()
Dim wkb As Workbook
Dim sht As Worksheet
Dim r As Range

    Set wkb = ThisWorkbook
    Set sht = wkb.Sheets("Sheet1")
    Set r = sht.Range("A1")
    Set r = sht.Range("A1")
    Set r = ThisWorkbook.Names("myRange").RefersToRange
    r.Value = 10
    [myRange].Value = 15
    sht.[myRange].Value = 30
    [myRange].Interior.ColorIndex = 6
    Set sht = wkb.Sheets("Sheet2")
    [myOtherRange].Interior.ColorIndex = 6
    'The below will only work on the ACTIVESHEET
End Sub

Open in new window

PS - I don't get the "Compile Error" that you raised in your OP.  Can you share some code that is getting that error?

AndresHernandoAuthor Commented:
Hi dlmille,
thanks for your help with this.  Here's some of the code that's triggering the error.
The curious thing is that it used to work fine on all machines.  Now it only works on mine.
Makes me suspect that there's some kind of setting that I inadvertently switched.

Private Sub Worksheet_Activate()
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:="1234"
    Application.Calculation = xlCalculationManual
    Dim tempValue As String
    '//Format LOJ preview "button"//
   '//Set ws view parameters//
   With ActiveWindow
        .DisplayGridlines = False
        .DisplayHeadings = False
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = False
    End With

    [R_wsBas_DeveloperCols].Columns.Hidden = True
    '//Hide remaining columns --Note: this is prone to error "Cannot Shift Objects Off Sheet"//
    On Error Resume Next
    [R_wsBAS_RemainingCols].entireColumns.Hidden = True
    [R_wsBAS_RemainingRows].entireRows.Hidden = True
    On Error GoTo 0

    ActiveWindow.Zoom = 100
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 1

    '//Freeze Panes so only Row1 will stay at top when user scrolls down//
    With ActiveWindow
        .FreezePanes = False
        .ScrollRow = 1
        .ScrollColumn = 1
    End With
    Cells([c_wsBAS_FreezePanes].Row + 2, 1).Select
    ActiveWindow.FreezePanes = True

    '//Set Row heights//
    ActiveSheet.Protect Password:="1234"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Open in new window

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

no compile err here.  Let me see if I can't get us help from someone who's seen this, before...

for a minute, just deselect all your references in the Tools->References areas for this workbook.  Then do a Debug/Compile...

same result?

AndresHernandoAuthor Commented:
it doesn not  allow me to de-select various references.  Gives warning: "Can't remove control or reference.  In use".
These are the refs that I couldn't de-select:
Visual Basic for Applications,
Microsoft Excel 12.0 Object Library,
OLE Automation,
Microsoft Forms 2.0 Object Library,
Microsoft Calendar Control 2007.

ALSO... I've attached my full file.  Upon opening and selecting "enable macros", it bugs out (but it never used to happen, and it works fine on my machine). RCOT-110921-1749.xlsm
Make sure you aren't in break mode in VBA when trying to add/delete references...

Are you sure you aren't getting the compile error on a reference library you're no longer linked to?  Maybe its not the [] reference that's causing the problem.

Unfortunately, some stuff is password protected, so I can't see the project library references your workbook is trying to make...
When I try to check your references, I am asked for the password for RCOT. What is it?

When I allow the workbook to open, I get an error in the Workbook_Open sub on this statement:
    [f_wsADM_ShowHintsOnSheetActivate].Value = True

I believe this named range is on worksheet AdminMisc, but the Immediate pane shows that worksheet Approvals is active. The Workbook_Open code worked correctly when the worksheet was fully qualified:
    With Worksheets("AdminMisc")
        .[f_wsADM_ShowHintsOnSheetActivate].Value = True
        .[f_wsADM_ShowHelpfulHintsOnSelectionChange].Value = True
    End With

Open in new window

AndresHernandoAuthor Commented:
The password is  "password"  (sorry for the delay)

The code that's bugging out worked perfectly on all computers for months.
Ok - I had a long list of "patches" I was making to get this to compile.  This workbook is obviously a work in progress....

E.g.,  Code stub:

Public Sub CloseConnection()
  If Not (cnxn Is Nothing) Then
    If cnxn.State <> adStateClosed Then
      If cnxn.State = 1 Then cnxn.Close
    End If
    Set cnxn = Nothing
  End If
End Sub

'--------------------insert record into a MS SQL DB

Dim stDocName As String
Dim stLinkCriteria As String
Dim strSQLQuery As String
OpenConnection  ' runs the connect string I show above.

rstINSERT.Open "DBO.LOGCAP_PROJECT_TABLE", cnxn, adOpenForwardOnly, adLockPessimistic

         rstINSERT![PPR_NUMBER] = Range("D1").Select
         rstINSERT![PPE_DESCRIPTION] = Range("D2").Select
         rstINSERT![PPR_AMOUNT] = Range("D3").Select
         rstINSERT![PPR_DATE] = Range("D4").Select
End Sub

Open in new window

That's not going to fly...

May I ask if you uploaded a working copy - can go check prior versions and see if one of them is working?  While this code may SOMEHOW run, there's a bunch of it that will not/SHOULD not run.

Try to DEBUG COMPILE and you'll begin to see what I mean.  I'm happy to highlight the syntax issues, but first want to raise a question as to the viability of this version (perhaps a WIP that somehow made it into production?)

While you think on this, I'll focus on your primary question from another angle.


AndresHernandoAuthor Commented:
I just pasted a version of the same file, but now with every range name syntax changed to  Range.("  ")

Now the code fails on...
 (1) CldrBasicInfo.Value = Date    ...if I change 'Date' to "Null" it works fine
 (2) Application.calculate   ...clicking "run" a SECOND time executes this line w/o bugging out.
 (3) Left(sTxt,11)         ..."Can't find project or library"

References shows "MISSING: Microsoft SOAP Type Library v3.0"
....but I thought that SOAP 2.0 and later was only needed in machine with source program.   In any event, this was working fine.

On a third machine, the Reference that shows missing is "MISSING: Microsoft Calendar Control 2007"

Could it be that some of my sheets have too many lines of code?  I read somewhere that after "x" many lines of code (or number of text characters) goofy stuff begins to happen? RCOT-110922-1128.xlsm
Something goofy has happened....

Does this application write its own VBA code to execute?

Can you find a version from a backup?

FYI --Excel works this way.  If there's a MISSING library, you could get errors on functions that are in the checked library (e.g., LEFT is in the Excel Object library - so unclicking the Missing Calendar control, enables Excel to finish its check and actually find that LEFT is no problem, whatsoever)...

Perhaps SOAP is not on that machine.  Check the references, scroll down and see if there's a Microsoft SOAP type library in the list, further down...

Maximum lines:

Code Limitations

The amount of code that can be loaded into a form, class, or standard module is limited to 65,534 lines. A single line of code can consist of up to 1023 bytes. Up to 256 blank spaces can precede the actual text on a single line, and no more than twenty-four line-continuation characters ( _) can be included in a single logical line.
Procedures, Types, and Variables
There is no limit on the number of procedures per module. Each procedure can contain up to 64K of code. If a procedure or module exceeds this limit, Visual Basic generates a compile-time error. If you encounter this error, you can avoid it by breaking extremely large procedures into several smaller procedures, or by moving module-level declarations into another module.
Visual Basic uses tables to store the names of identifiers (variables, procedures, constants, and so on) in your code. Each table is limited to 64K.


AndresHernandoAuthor Commented:
dlmille, thanks for looking into this.
(1) No, the code doesn't write any code
(2) I am attaching two backup files.  They are from consecutive days.  
   (a) The last day that works  
   (b)The first day that fails.
(3) None fail (they all work fine) on the originating machine that was used to write the code.
(4) Machines that fail show different libraries missing!  One shows SOAP missing, the other shows calendar library missing.
AndresHernandoAuthor Commented:
dmille,  second file here...

This is the first day that fails.
Ok.  I installed the calendar control and tried the "this one works" app.  It loaded just fine (still buggy code issues re: debug/compile, but let's move on).

I tried the "this one fails" and it opens just fine as well.

What is failing for you?

I went to the BasicInfo tab where the code is you said fails (re: prior post).  no problem...

If both of these work on your one machine.  And they work on mine.... Hmmm.....

Reboot one of those that failed and try again....

On one that failed that had the fewest missing libraries - add the libraries back (e.g., download the calendar control or get a copy where you have it and re-register it).

You can find the calendar control 11.0 here: http://www.gmayor.com/downloads.htm in the MSCAL.ZIP file is instructions on how to install...

Have any of the machines gotten an Office upgrade?  Any running 64 bit Office?  Calendar control won't run on 64 Bit Office.

Rory ArchibaldCommented:
FWIW, using the [A1] notation is inefficient - it's a shorthand for evaluate - so IMO you should avoid it.
You'll still need to fix your reference issues of course.
AndresHernandoAuthor Commented:
thanks for all your time and effort on this.  Your input has been a big help.    I'll run
I got it to work on other computers by changing all range refernces to the syntax    
Range.("rangename")      (vs. [  ]   )

byundt and Rory, thanks for your input on this too!

Many thanks, --Andres
hi everyone,

I know this thread is old/solved but I've just come across it via the "Excel Zone Expert Discussion, Number 30" thread & for the convenience of future searchers I'm adding the following links which explain a bit more about the use of square brackets being shorthand for Evaluate. I'm sure that it was Rory who originally explained it to me a couple of years ago but I can't find that specific thread.

Anyway, my opinion is the same as Rory's - avoid square brackets because of inefficiency & also the fact that intelli-sense doesn't work using the and the links are only to allow searchers to be more informed...

(where Chip Pearson states "While the [A1] syntax is easier to type, it is slower to execute than the Range("A1") syntax.")

(see the "Evaluate Method:" section)

(Aaron Blood gives a number of examples of the power & flexibility of Evaluate)

(note that Daniel states his usual objective when using square brackets is simplicity/clarity & not efficiency - see his table for some timing tests)

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now