none
Can't access macros in personal.xlsb workbook from a second workbook RRS feed

  • Question

  • I can only access macros in my personal.xlsb workbook from other macros in the same workbook.  When attempting to access these macros from a macro in a second workbook, I get the message "Compile Error - Sub or Function Not Defined."  In the Project section of the VBA window, I can see and manipulate all macros in the personal.xlsb workbook and see and manipulate all macros in the second workbook.  If I insert the modules that are in the personal.xlsb workbook into the second workbook, everything works OK.  What am I missing?
    Friday, September 20, 2013 3:22 PM

Answers

  • I had a similar problem.  I wound up renaming the add-in to JFM.xlam and also renaming it, and saving it, in the VBE after modifying the Name Property of the project.

    Ron

    • Marked as answer by JimFM Tuesday, September 24, 2013 7:25 PM
    Tuesday, September 24, 2013 5:49 PM

All replies

  • I'm not sure.  But it may be that you have some problems with duplicate names at either the module or macro level.

    Ron

    Friday, September 20, 2013 11:53 PM
  • Ron - Thanks for the reply.  I've checked and all macro & module names are unique.  I can copy the same modules that are in personal.xlsb to the second workbook, and the macros in these modules work OK.  This is with personal.xlsb still open.  I'd think this would give duplication errors as I've got identical modules and macros open in personal.xlsb and the second workbook.  It seems the macros in personal.xlsb are invisible to other open workbooks.
    Saturday, September 21, 2013 2:02 PM
  • I'm sorry I cannot help you with personal.xlsb as I'v never used it. 

    For the macros I want to have generally available, I save them as an .xlam file  and install them as an add-in.


    Ron

    Saturday, September 21, 2013 6:29 PM
  • Thanks, Ron - I'll give that a shot.  Just curious - Are you related to any Rosenfelds from Arkansas?  A friend's parents were Jerome & Ethyl Rosenfeld.
    Sunday, September 22, 2013 8:42 PM
  • No, no relations that I know of in Arkansas.  Our name only goes back to the early 1900's.  My grandfather changed his last name when he came through Ellis Island.

    Ron

    Monday, September 23, 2013 12:08 AM
  • Ron - I saved the macros in an .xlam file and enabled the file as an add-in.  I'm having the same issue as with the personal.xlsb file. I can only access the add-in macros from other macros in the add-in.  When attempting to access these macros from a macro in a second workbook, I get the message "Compile Error - Sub or Function Not Defined."  In the Project section of the VBA window, I can see and manipulate all macros in the add-in and see and manipulate all macros in the second workbook.  If I insert the modules that are in the add-in into the second workbook, everything works OK.  Do you have any thoughts?
    Monday, September 23, 2013 1:49 PM
  • Not off the top of my head.  If you could post the .xlam file on some publicly accessible site, like Sky Drive, and post a link here, I'd be glad to take a look at it.

    Ron

    Monday, September 23, 2013 5:19 PM
  • Ron - The file is called Add-In_JFM.xlam and I uploaded it as a public file to SkyDrive. When I tried to post the link here I got the message "Body content cannot contain images or links until we are able to verify your account."  In the quasi-link below, substitute "/" for each occurance of "<FORWARD SLASH>" and I think you'll get there.

    https:<FORWARD SLASH><FORWARD SLASH>skydrive.live.com<FORWARD SLASH>?cid=bbe495c1b9c37b6d#cid=BBE495C1B9C37B6D&id=BBE495C1B9C37B6D%21105. Do you need any other information?  Thanks

    Monday, September 23, 2013 5:54 PM
  • I cannot reproduce your problem.  If I install your macro, I can reference in another macro.

    In order to reference in another macro in another workbook, you need to set a reference.  Perhaps you didn't do that?

    You can set a reference by going to the Tools/References dropdown in the VBE main menu, with a module in your second workbook open.

    I used this simple macro to test, and it behaved as expected.  Perhaps you didn't set the reference?

    Sub foo()
        With Range("a3")
            .Value = "ABC"
            .Select
            colorred
        End With
    End Sub


    Ron

    Monday, September 23, 2013 10:46 PM
  • Ron - Thanks again.  Here's my latest snag.  I opened the References dialog box and didn't see my add-in, so I selected Browse and picked my add-in.  This made a "VBAProject" entry in the References dialog box that referred to my add-in.  When I checked the box beside it and clicked OK I got the message "Name Conflicts with existing module, project, or object library."  I removed the add-in from my workbook and tried again with the same result.  Any ideas?
    Tuesday, September 24, 2013 3:58 PM
  • I had a similar problem.  I wound up renaming the add-in to JFM.xlam and also renaming it, and saving it, in the VBE after modifying the Name Property of the project.

    Ron

    • Marked as answer by JimFM Tuesday, September 24, 2013 7:25 PM
    Tuesday, September 24, 2013 5:49 PM
  • Ron - You just solved my problem and made my day.  I can't thank you enough for all the help.
    Tuesday, September 24, 2013 7:26 PM
  • You're very welcome.  Thanks for the feedback.  I think you will find it simpler, in the long run, to have your personal macros in an add-in vs personal.xls file.


    Ron

    Tuesday, September 24, 2013 7:40 PM