Is there a way to have VBA import a XLSB?

[DataBlake]

I have a personal macro workbook and i don't want to translate everything on it/change current codes to another XLSM.
Is there a way to import my personal macro workbook XLSB automatically so that another user's XLSB is replaced by mine when opening a specific file?
as well as establish a reference to my XLSB?

 

[Fluff]

As Joe said, create an Add-in.

 

[DataBlake]

never done that, but will attempt .....monday

 

[Greg Truby]

First of all, if you were to send me a workbook that would attempt to overwrite or replace *my* personal macro workbook with yours, I would most certainly not react very kindly. You should never seek any solution that would destroy or rename files on a user's computer without them knowing exactly what you're going to do.

But overwriting a user's personal macro workbook is simply not necessary. As Joe and Fluff have indicated, you can certainly create your own add-in. But, shucks, that ain't even necessary; a feller can create a normal workbook. Just create a bog-standard XLSM workbook and put a dab of code in the workbook's _OPEN() event handler:

Private Sub Workbook_Open()
    Windows(Me.Name).Visible = False
End Sub

Then just save the workbook to the user's XLSTART folder and voilá, you're done. They have the workbook open every time they start Excel and it's hidden from view. If you're not familiar with them, you've got a pair of XLStart Folders to pick from:

C:\Program Files (x86)\Microsoft Office\OfficeNN\XLSTART
C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART

The difference, in case it ain't obvious, is that if'n ya got more'n one chap using that computer the first XLSTART would get used by everyone. If you have multiple users on the same computer, but you only want one (or a select few) to automatically load this workbook, then you'd use the second path.

 

[DataBlake]

First of all, if you were to send me a workbook that would attempt to overwrite or replace *my* personal macro workbook with yours, I would most certainly not react very kindly.

The users using this workbook program don't know what a personal macro workbook is, enduser is not working directly in excel as i am.

You should never seek any solution that would destroy or rename files on a user's computer without them knowing exactly what you're going to do.

I didn't truly mean "replace" but rather alternatively directed to. poor choice of wording.

Then just save the workbook to the user's XLSTART folder and voilá, you're done. They have the workbook open every time they start Excel and it's hidden from view. If you're not familiar with them, you've got a pair of XLStart Folders to pick from:

C:\Program Files (x86)\Microsoft Office\OfficeNN\XLSTART
C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART

The difference, in case it ain't obvious, is that if'n ya got more'n one chap using that computer the first XLSTART would get used by everyone. If you have multiple users on the same computer, but you only want one (or a select few) to automatically load this workbook, then you'd use the second path.

this is the solution i thought of before an addon. thanks for the folder clarification i didn't know the difference.
if addons aren't my flavor i will use this method.

 

[Greg Truby]

Just so's ya know -- if'n ya opt fer the add-in path you'll need to give the user some kinda way to access the macro. If you go with the hidden workbook option (providing you don't make the module or subroutine Private) the macro shows up in the Run Macro dialog box (the one you get when you hit Alt+F8). Macros in add-ins do not show up in the Run Macro dialog box.

This, of course ain't really all that big a deal. You can customize the user's QAT with a "macro" button set to show for all workbooks and in the customization dialog, you can tie that button to macros in hidden workbooks or add-ins.

Or, of course, you can use the CustomUI editor to create your own interface elements somewhere in the ribbon. But if you've never written your own add-in, I'm guessing you probably have not done a great deal of custom user interface creation.

 

[DataBlake]

Just so's ya know -- if'n ya opt fer the add-in path you'll need to give the user some kinda way to access the macro

these specific macros (the dictionaries) are unseen to enduser. The macros to call lookups to the dictionaries are already in their sheets as buttons.
it looks like the add-in accomplishes what i want. I can even perform a file exists check and install it if its not there already.

 

[DataBlake]

Looks like i spoke too soon.
@Fluff and @Joe4 am i not able to use public variables with an add-in? I got the add-in to replace the code i had for my personal macro workbook, but when i try to call to the public dictionaries i get an object required error.

 

[Fluff]

I suspect it can be done, but as I've never created an add-in, I don't know.

 

[DataBlake]

I don't know.

 

[Greg Truby]

am i not able to use public variables with an add-in? ... but when i try to call to the public dictionaries i get an object required error.

Without seeing any actual code from the workbook(s) involved we can only provide guesses. However, if I'm reading between the lines correctly, you're trying to access variables between VBA projects (workbooks)? If that's the case, you should know that public means that the variable is visible throughout the same project; it does not make the variable visible to other VBA projects (workbooks' code) — at least AFAIK. If someone else has ever done so, I'd be interested to see it done.

It is possible to make data available between projects by stashing the values into those parts of the foreign workbook that can be read externally. I did similar types of things when developing complex add-ins for clients. However, I only read a fairly limited pallet of properties (values) from external workbooks. If you could provide a bit more detail, we might be able to make more specific suggestions.