Solved

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

Posted on 2011-09-21
19
734 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:AndresHernando
19 Comments
 
LVL 41

Expert Comment

by:dlmille
ID: 36575183
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

or

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

----------------------------------------------------------------------------------------------------------------

HOWEVER, your use of:

[range].select

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")
    
    sht.Range("A1").Select
    Set r = sht.Range("A1")
    r.Select
    
    Set r = ThisWorkbook.Names("myRange").RefersToRange
    
    r.Select
    r.Value = 10
    
    [myRange].Select
    
    [myRange].Value = 15
    
    sht.[myRange].Select
    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
    sht.[myOtherRange].Select
    
End Sub

Open in new window


Dave
fun-with-shortcuts-r1.xls
0
 
LVL 41

Expert Comment

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

Dave
0
 

Author Comment

by:AndresHernando
ID: 36578065
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.

--Andres
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"//
    M_RaisedEdges_wsBAS_PreviewLOJ
    [a1].Select
       
    
   '//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//
    Application.Calculate
    M_SetRowHeight_wsBAS
    
    ActiveSheet.Protect Password:="1234"
    Application.EnableEvents = True
    Application.ScreenUpdating = True
   
       
End Sub

Open in new window

0
 
LVL 41

Expert Comment

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

Dave
0
 
LVL 41

Expert Comment

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

same result?

Dave
0
 

Author Comment

by:AndresHernando
ID: 36578206
dlmille,
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36578256
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...
0
 
LVL 80

Expert Comment

by:byundt
ID: 36578270
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

0
 

Author Comment

by:AndresHernando
ID: 36578682
The password is  "password"  (sorry for the delay)

The code that's bugging out worked perfectly on all computers for months.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Expert Comment

by:dlmille
ID: 36578740
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
     
'SETUP AND OPEN CONNECTION
    
OpenConnection  ' runs the connect string I show above.
    
'RUN SQL INSERT COMMAND

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

    cnxn.BeginTrans
    rstINSERT.AddNew
         rstINSERT![PPR_NUMBER] = Range("D1").Select
         rstINSERT![PPE_DESCRIPTION] = Range("D2").Select
         rstINSERT![PPR_AMOUNT] = Range("D3").Select
         rstINSERT![PPR_DATE] = Range("D4").Select
    rstINSERT.Update
cnxn.CommitTrans
CloseConnection
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.

Cheers,

Dave
0
 

Author Comment

by:AndresHernando
ID: 36578745
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36578750
Something goofy has happened....

Does this application write its own VBA code to execute?

Can you find a version from a backup?

Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36578774
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.


http://msdn.microsoft.com/en-us/library/aa240819(VS.60).aspx

Dave
0
 

Author Comment

by:AndresHernando
ID: 36578972
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.
RCOT-110906-1433-Works.xlsm
0
 

Author Comment

by:AndresHernando
ID: 36578992
dmille,  second file here...

This is the first day that fails.
RCOT-110907-0826-Fails.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36579094
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.

Dave
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36580508
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.
0
 

Author Comment

by:AndresHernando
ID: 36896147
dlmille,
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
0
 
LVL 10

Expert Comment

by:broro183
ID: 37060069
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...

http://www.cpearson.com/excel/optimize.htm
(where Chip Pearson states "While the [A1] syntax is easier to type, it is slower to execute than the Range("A1") syntax.")

http://www.decisionmodels.com/calcsecretsh.htm
(see the "Evaluate Method:" section)

http://www.ozgrid.com/forum/showthread.php?t=52372
(Aaron Blood gives a number of examples of the power & flexibility of Evaluate)

http://smurfonspreadsheets.wordpress.com/2008/03/26/getting-a-range/
(note that Daniel states his usual objective when using square brackets is simplicity/clarity & not efficiency - see his table for some timing tests)

Rob
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now