Excel VBA Compile Error

Question

We have an excel spread sheet which we use and it works for most machines but bombs out with 'Compile Error in Hidden Module - General' on others, and the reason appears to be due to missing References.

We check that Macros is enabled but still doesn't help.

Since we protect this excel spread sheet with a password, we don't want to be giving this password out to our users to check the References, and wondered if anyone had any idea how I can add VBA code to check whether the References required for the excel spread sheet is there and if not then bring up a message box to advise the user.

The References we use are as follows:

Visual Basic For Applications
Microsoft Excel 11.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft Windows Common Controls 5.0 (SP2)

Alternatively, if anyone has any other suggestions on how to go about this problem, that would be great.

I don't know if there's vba for checking references, but I know that the excel object library is often different depending on the version of office installed. My suggestion would be to use late binding if the commands are the same...but there's probably a better way. — RowanC
– RowanC, Commented Aug 8, 2013 at 1:27
Hi mate, I'm still quite new to VBA programming, so what do you mean by using late binding? — SamoanProgrammer
– SamoanProgrammer, Commented Aug 8, 2013 at 1:40
thank you @SamoanProgrammer would you know how your users can add references if there is a password on the VBA code? (I have the exact same problem). chrs, BK — BenKoshy
– BenKoshy, Commented May 30, 2016 at 21:50

Dick Kusleika · Accepted Answer · 2013-08-08 13:55:55Z

2

The only reference you have listed that could possibly be missing is the common controls. The rest are default in every version of Excel. The Forms one is only if you have a userform or explicitly set it, but that's not your problem. Common Controls is your problem. It doesn't ship with Office anymore. If you have Visual Studio or VB6 you probably have it. Or an old version of Office like XP Developer Edition.

Anyway, you can check for the existence of the OCX file in the System folder. I don't think it's required to be in that folder, but I've never seen it anywhere else.

It's been quite a while since I've seen a reference to 5.0, so I included how to find 6.0 in the code below. Check to make sure you know what version you're using.

In a standard module:

Private Declare Function GetSystemDirectory Lib "kernel32" Alias "GetSystemDirectoryA" (ByVal lpBuffer As String, ByVal nSize As Long) As Long

Public Function HasCommonControl() As Boolean

    Dim sFolder As String
    Dim lReturn As Long

    Const lSIZE As Long = 255
    Const sDLLNAME As String = "COMCTL32.OCX" 'For windows common control 5.0
    'Const sDLLNAME As String = "MSCOMCTL.OCX" 'For windows common control 6.0

    sFolder = Space(lSIZE)
    lReturn = GetSystemDirectory(sFolder, lSIZE)

    sFolder = Left$(sFolder, lReturn)

    HasCommonControl = Len(Dir(sFolder & Application.PathSeparator & sDLLNAME)) > 0

End Function

Having said all that, why are you using common controls? If it's for a treeview on a userform, then check out this all-vba treeview

http://www.jkp-ads.com/articles/treeview.asp

Since jkp wrote that, I haven't used common controls. So few normal-people PCs have it installed that it's just a pain.

answered Aug 8, 2013 at 13:55

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SamoanProgrammer Over a year ago

I did find the jkp example online as we do have a treeview which is empty when running in Excel 2010. Basically, we're working with an excel template which was created a number of years ago and it was all working fine until later releases of Excel. The problem I was having was also because of 64 bit machines, so I've added in some code to check If VBA = 7 then run the 64 bit code otherwise use code that works in 32 bit.

Steve Rindsberg · Accepted Answer · 2013-08-08 02:04:49Z

Depending on a reference to a specific Excel or other component version is one possible problem. Switching to late binding would solve that problem, so long as you're careful not to use any commands/objects/methods/properties that are supported in one version and not another.

Following up on RowanC's link (good choice), you can add a reference to Excel, for example and write your code like so:

Dim xlWks as Excel.Worksheet
'Dim xlWks as Object

Once everything's debugged, remove the Excel reference and change the declarations to:

'Dim xlWks as Excel.Worksheet
Dim xlWks as Object

That gives you the benefit of intellisense while coding/debugging but removes the dependency on a specific version of Excel later.

Might be mistaken, but IIRC the Common Controls component is part of Visual Basic, not Office, so unless you distribute it and register it along with your app, it might not be present on some systems.

RowanC · Accepted Answer · 2013-08-08 01:56:50Z

0

To try late binding, which doesn't need the references setup (although it may have a performance hit, and it will mean that autocomplete doesn't work in your code) instead of calling excel in the following way:

Dim oExcel As Excel.Application
  Set oExcel = CreateObject("Excel.Application")

try calling it this way, and drop the reference to the excel object model.

 Dim oExcel As Object
   Set oExcel = CreateObject("Excel.Application")

answered Aug 8, 2013 at 1:56

RowanC

1,65911 silver badges18 bronze badges

Collectives™ on Stack Overflow

Excel VBA Compile Error

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related