0

I found this code from spreadsheetguru. The purpose of the code is to activate the Excel 16.0 Library. I want to be able to add this before my code so that it automatically adds the Library and then runs the rest of the code that requires it. Right now I have it in two subs. One is the Private Sub that I have here, and the next is a Public Sub with the rest of my macro. Right now when I run the macro it runs the code psoted here, and then stops. Then If I click again it will run the next sub. Is there any way for me to make this all just go in one click? Maybe by making it all one sub, or some other Private/Public Sub combination I'm not aware of?

Thanks!

Private Sub ActivateReferenceLibrary()

'PURPOSE: Activate Specific Object Libraries

'Error Handler in Case Reference is Already Activated
  On Error Resume Next

    'Activate PowerPoint Library (version 2.9)
        Application.VBE.ActiveVBProject.References.AddFromGuid _
          GUID:="{00020813-0000-0000-C000-000000000046}", _
          Major:=0, Minor:=0      

'Reset Error Handler
  On Error GoTo 0

End Sub
ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Pinlop
  • 245
  • 2
  • 16
  • 1
    What have you tried so far? Have you tried Call ActivateReferenceLibrary from the other Sub? See here for some info on scope when calling a subroutine https://www.thespreadsheetguru.com/blog/2014/3/5/explaining-private-vs-public-declarations – QHarr Sep 26 '17 at 23:15
  • the code that you posted only runs the one command, nothing else, so why are you expecting it to do more? – jsotola Sep 27 '17 at 01:12
  • @QHarr these are the three ways I tried: [Call from ActiveReferenceLibrary](https://pastebin.com/3Fk4DnnC) , [Call from averageScoreRelay](https://pastebin.com/7nVweNPr) , [No Call, just Public Subs](https://pastebin.com/Eq1pqVjc). None of these are working. They just give me errors like: Sub or Function not defined, User-defined type not defined, or it gives me no error and just runs the ActiveReferenceLibrary and stops. – Pinlop Sep 27 '17 at 14:13
  • Have a look at `late binding` which won't be so version specific. If someone has Excel 2010 installed it will never find the Excel 16.0 library, but there's a very good chance the code will run on the Excel 14.0 library instead. – Darren Bartrup-Cook Sep 27 '17 at 14:58
  • @DarrenBartrup-Cook I really just want to get this way to work. Late binding also slows down code, and my code is already slow. – Pinlop Sep 27 '17 at 15:18
  • 1
    It maybe just me, if so i apologise, but perhaps edit the question to make it clearer you are working in Powerpoint 2016 . – QHarr Sep 27 '17 at 15:32
  • @QHarr no problem, but the question wouldn't change if I was starting in Excel and trying to add a PowerPoint 16.0 Library reference. Imagine the question was: when starting from Excel I can get the first piece of code to add a PowerPoint 16.0 reference when alone, but I don't know how to make my main code call upon ActiveReferenceLibrary without crashing. – Pinlop Sep 27 '17 at 15:40

1 Answers1

1

In the article spreadsheetguru it is given as Sub ActivateReferenceLibrary() so you need to change your private sub to just sub. Then in your other Sub, i think called averageScoreRelay, at the start you would write this line: Call ActivateReferenceLibrary. Make sure both Sub routines are in the same workbook and in standard modules (you can place them in the same standard module).

Bear in mind, some library references are default and cannot be removed. Though you would get a different error message. If you are using Excel 2016 for example, the Excel 16.0 object library will automatically be present by default. You can't add this in to earlier version or remove from 2016 (i believe).

Failing that please post more of your code so we can see how your other Subroutine is working.

Some other solutions, such as late binding where you can remove certain reference dependencies, are discussed here: VBA reference libraries

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • I still keep getting the same errors :/ [here is my full code](https://pastebin.com/gFDYTtAq). I'm starting from PowerPoint and going into Excel, that's why I need the Excel Library. I want to avoid late binding as it will slow down my already slow code by double the time at least. But besides just the time, I just really want to try and get this way to work. – Pinlop Sep 27 '17 at 15:17
  • Can you confirm which library reference specifically is missing that you want to add and which version of MS office are you using? Excel 2016 object library will be present by default if you have MS Office 2016 installed. – QHarr Sep 27 '17 at 15:22
  • I have office 365 and I'm looking to add he Excel 16.0 Library. When starting from PowerPoint it only has PowerPoint 16.0 present by default. – Pinlop Sep 27 '17 at 15:27
  • did you have to run the program more than once? Or did it run through everything in one go? – Pinlop Sep 27 '17 at 15:41
  • Ahh.. i get where you are coming from. At compiling it doesn't yet have the reference (which is added in the later call) which is why it is giving you that error. I don't know if there are options for adding the add reference sub into an event such as opening the presentation. There was some discussion here: https://stackoverflow.com/questions/11306007/how-to-auto-execute-a-macro-when-opening-a-powerpoint-presentation – QHarr Sep 27 '17 at 15:46