Setting Reference Library within Macro

[treehugger87]

Afternoon,

I have a workbook with two macros that don't get along. If the reference needed for the first is active, then the second crashes. So, I'm trying to manually set the reference within the first macro, then deactivate it at the end using a GUID.

Please Login or Register  to view this content.

It crashes on the Set refMSO12 = New References with a Run-Time Error 429: ActiveX component cannot create object. If I take that line out, it does actually add in the library, but also gives another errror of 438: Object doesn't support this property or method (doesn't highlight whats wrong either).

Thanks much for your help!

Carl

[romperstomper]

You want something like:

Please Login or Register  to view this content.

or of course you could just late bind.

[treehugger87]

Okay changed it, and it looks like its sort of setting it up. I'm using a msoFileDialogFolderPicker, reliant on 12.0 object library, which fails. But if you check after the reference is selected. So, I'm a tad confused.

Isn't this late binding since I'm not checking each of the references off in the Tools --> References box?

[romperstomper]

Can you clarify why you need the Office reference for that? It's an Excel thing.
And no, if you were late binding, you wouldn't need references to be set at all.

[treehugger87]

When you type msoFileDialogFolderPicker into the VB editor it pops up with a message saying it needs to add another reference, if it isn't added a method failed error message comes up when the program is run.

[romperstomper]

Ah yes - I forgot that although it's Application.Filedialog, the return type is Office.FileDialog

Why can't you just leave the Office reference in place though? It should be forward and backward compatible for different versions.

[treehugger87]

Its generating an error within the second macro whenever that reference is selected; Compile Error: Assignment to constant not permitted, and highlights msoShapeRectangle = 1. If you keep them separate its fine, but it'd be much nicer if they were all contained within a single workbook.

I don't want to mess around with the code of second macro as it's running statistics on samples and I can't impact the way it handles all that. So, I was trying to make the other macro compatible.

[romperstomper]

Why are you trying to assign a value to a built-in constant? It is already 1.

[treehugger87]

Haha I have no idea, I didn't make it. But it works as long as I don't fiddle with it.

[romperstomper]

If you removed that line altogether, it should work with your Office reference set.

[treehugger87]

Doesn't seem to be working without that line. So, I guess I'll stick with trying to turn the reference on and off.

Is their a lag period between turning it on and using it? The program gives an error like it hasn't turned on (mso folder picker gets mad) but when you check the references box the office 12 object library is selected.

Please Login or Register  to view this content.

Thanks!

[romperstomper]

It should work just fine as long as you have the Office reference set.
I think we should probably be reviewing the other macro since that's the one causing the headache...

[treehugger87]

Hey I actually got it working. It's better now that the addition and removal of the references are done in separate subs.

Please Login or Register  to view this content.

Thanks for all the help!

Carl