Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm using an Excel based tool that can do two (related) tasks. For one of the tasks, a special call is required that only works if I have a Reference added to a certain AddIn (an XLA file). Some of the users require this functionality, some not.

    Problem is that if people do not have this software to whch the XLA belongs loaded (and thus the specific AddIn does not exist), the moment they load my tool, there is an error as the calls in my code to functions available in the specific AddIn do not exist [the standard "compile error in hidden module" message]. (and of course, the AddIn is also marked as "MISSING" in the References list).

    I'd like the tool to always work and e.g. give a nice message when a certain call is not supported.

    So I thought, let's get rid of the direct Reference to the Add-In and add some code in the Auto_Open that checks which Add_ins are loaded and installed. If it's available and not installed, I do that, alse I set a flag. I have the code and it works.
    Seems logical? No, because even with that code, the calls to the functions in the AddIn still exist in my tool and I still get an error (not upon sheet loading I think, but as soon as I run it, which is even more strange as in my case even though the AddIn isn't referenced in my Tool anymore, it IS loaded and installed as AddIn in my Excel... this means that my "check and load AddIn code" wouldn't work anyway... right?).

    Mystery (for me): the Add-In is always loaded in my Excel but I must have it Referenced in my VBA code to make it work ????

    So maybe a better way (??) is to do late binding... although I've done that before, I have no idea how that would work with an XLA file that is referenced.

    Anyone who can shed some light on this and help me escape?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To run code that is not in the active workbook or built into Excel, you need to set a reference to the relevant VBA project. Just loading an add-in does not automatically set a reference to its VBA project. A well-known example is the Analysis Toolpak - VBA add-in that makes the functions from Analysis Toolpak available to VBA. Installing this add-in adds a reference to atpvbaen.xla to the list but does not set it (i.e. tick its check box). You have to do that yourself.

    You can't use late binding - the add-in is not an Automation object.

    Try the following:
    - Isolate ALL code that directly uses constants, functions or procedures from the add-in in a separate module.
    - Only call functions or procedures from this module if you know that the add-in is installed.

    In other words, do NOT use

    Code:
    If Application.AddIns("MyAddin").Installed Then
      Call ProcedureInAddIn
    Else
      Beep
    End If
    but
    Code:
    If Application.AddIns("MyAddin").Installed Then
      Call ProcedureInSeparateModule
    Else
      Beep
    End If
    where ProcedureInSeparateModule is a sub in your separate module that calls ProcedureInAddIn.

    Of course, you could also create two versions of your tool, but that's hell to maintain...

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796007' date='02-Oct-2009 12:26']To run code that is not in the active workbook or built into Excel, you need to set a reference to the relevant VBA project. Just loading an add-in does not automatically set a reference to its VBA project. A well-known example is the Analysis Toolpak - VBA add-in that makes the functions from Analysis Toolpak available to VBA. Installing this add-in adds a reference to atpvbaen.xla to the list but does not set it (i.e. tick its check box). You have to do that yourself.

    You can't use late binding - the add-in is not an Automation object.

    Try the following:
    - Isolate ALL code that directly uses constants, functions or procedures from the add-in in a separate module.
    - Only call functions or procedures from this module if you know that the add-in is installed.

    In other words, do NOT use

    Code:
    If Application.AddIns("MyAddin").Installed Then
      Call ProcedureInAddIn
    Else
      Beep
    End If
    but
    Code:
    If Application.AddIns("MyAddin").Installed Then
      Call ProcedureInSeparateModule
    Else
      Beep
    End If
    where ProcedureInSeparateModule is a sub in your separate module that calls ProcedureInAddIn.

    Of course, you could also create two versions of your tool, but that's hell to maintain...[/quote]

    Hans, thanks for your response. I understand your explanation that being loaded is not enough and it should be referenced.

    What I'm still not understanding is your proposed solution: if I locate the stuff in a separate module (in my Workbook) that wouldn't change anything to the fact the the reference to the XLA must be set? Are are you implying there are two separate files required: one with the whole always working code (and no references checked) and one with only the code that calls the specific XLA? But then how can I call that code if the sheet isn't loaded... sorry... I'm still a bit lost here

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You should *not* set the reference manually.

    In the Workbook_Open event of the workbook, check whether the add-in is available, and if so, set the reference using

    ThisWorkbook.VBProject.References.AddFromFile "filename"

    My suggestion was to place all code that directly refers to the add-in in a single module in your workbook, and call the subs and functions in this module from the other modules in your workbook only if the flag has been set that signals that the reference has been set. That way, the module with the code that refers to the add-in is never used if the add-in is not available.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='796016' date='02-Oct-2009 13:32']You should *not* set the reference manually.

    In the Workbook_Open event of the workbook, check whether the add-in is available, and if so, set the reference using

    ThisWorkbook.VBProject.References.AddFromFile "filename"

    My suggestion was to place all code that directly refers to the add-in in a single module in your workbook, and call the subs and functions in this module from the other modules in your workbook only if the flag has been set that signals that the reference has been set. That way, the module with the code that refers to the add-in is never used if the add-in is not available.[/quote]

    Yep... I think I got it working

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ErikJan View Post
    Yep... I think I got it working
    Sh... now some users get errors. I debugged and know why, next question is of course what to do about this.. OK, here's my code (stolen from here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=267).

    In order to add a refence, it starts with cleaning out those that pointto addins not loaded on the current system:
    On Error Resume Next 'Set to continue in case of error
    'Remove any missing references
    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
    Set theRef = ThisWorkbook.VBProject.References.Item(i)
    If theRef.isbroken = True Then ThisWorkbook.VBProject.References.Remove theRef
    Next

    Some people get the error: "Runtime Error "1004" / "Programmatic access to Visual basic Project is not trusted". I found to cause too... in the Tools / Macros / Security on the second tab there are two check-boxes at the bottom. The lower one is called "Trust access to Visual Basic Project". When that one is not checked, the error pops-up (and the loop hangs, and even if I'd break out the next loop and therefore the whole purpose of this section would fail).
    Is there any sensible workaround for this? Why would someone have this option disabled (if macro security is e.g. set to Medium)?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Ten years ago or so, macro viruses for Office applications were quite popular. Turning off "Trust access to Visual Basic project" helped to prevent a macro virus from adding or altering code. I think it became the default setting.

    For obvious reasons, you can't enable "Trust access to Visual Basic project" from code.

    If the users are within your organization, IT can turn on "Trust access to Visual Basic project" using a group policy.
    Otherwise, you'll have to instruct the users how to change the setting themselves.


  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think I saw that one coming ...

    So next to having my IS dept change something (which they will probably never do, just for me), or asking users to switch an obscure setting on somewhere deep down (for them), would there be an alternative way to solve my AddIn problem that avoids this?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I can't think of an alternative.

    Turning on "Trust access to Visual Basic project" isn't that difficult - you could provide step-by-step instructions with screenshots.


  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by ErikJan View Post
    I think I saw that one coming ...

    So next to having my IS dept change something (which they will probably never do, just for me), or asking users to switch an obscure setting on somewhere deep down (for them), would there be an alternative way to solve my AddIn problem that avoids this?

    Found this; can I convert this to VBA somehow?:

    Set wsh = CreateObject("WScript.Shell")
    'key to modify
    str1 = "HKEY_LOCAL_MACHINE\Software\Microsoft\Office\ " & _
    Application.Version & "\Word\Security\AccessVBOM"
    'enable access
    wsh.RegWrite str1, 1, "REG_DWORD"
    'read the vba project name
    MsgBox Application.NormalTemplate.VBProject.Name
    'disable access
    wsh.RegDelete str1

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It *is* VBA, but it's for Word and it's the wrong branch of the registry.

    Use this:

    Set wsh = CreateObject("WScript.Shell")
    str1 = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
    Application.Version & "\Excel\Security\AccessVBOM"
    wsh.RegWrite str1, 1, "REG_DWORD"

    But I don't know whether this will have immediate effect, I suspect it'll only work next time the user starts Excel.

  12. #12
    New Lounger
    Join Date
    Jun 2013
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Office VBA Access Protection

    Quote Originally Posted by HansV View Post
    It *is* VBA, but it's for Word and it's the wrong branch of the registry.

    Use this:

    Set wsh = CreateObject("WScript.Shell")
    str1 = "HKEY_CURRENT_USER\Software\Microsoft\Office\" & _
    Application.Version & "\Excel\Security\AccessVBOM"
    wsh.RegWrite str1, 1, "REG_DWORD"

    But I don't know whether this will have immediate effect, I suspect it'll only work next time the user starts Excel.
    This process will only work from a script once the Office Application is shut down.

    Refer: http://blogs.msdn.com/b/cristib/arch...ng-macros.aspx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •