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?

 

[DataBlake]

So when i open my main workbook it installs the addin and then runs the code from the addin via userform
but before this all i did was call my XLSB that i had referenced: Application.Run "PERSONAL.XLSB!ShowUserform"

'in main workbook
Sub Workbook_Open()

    If FileExists(Application.UserLibraryPath & "\Blek.xlam") = True Then
        If AddIns("Blek").Installed = True Then
        'do nothing
        Else
        AddIns("Blek").Installed = True
        End If
    Else
    FileCopy Application.ActiveWorkbook.Path & "\Program Files\Blek.xlam", Application.UserLibraryPath
    AddIns("Blek").Installed = True
    End If
    
Application.Run "Blek.xlam!ShowUserform"
End Sub

Here is what i had in my XLSB: my userform would call macros like this
and this WORKED because i had referenced it.

'in XLSB
Public dicPRICE As Object

Sub DictionaryPRICE()

'establish sheet arrays and dictionary object
Dim openWB As Workbook, ws As Worksheet
Dim filePATH As String, wb As String
Dim ary1 As Variant, ary2 as variant
Dim i As Long, x as long

Application.ScreenUpdating = False

'code to open dictionary values workbook, contains codes that grab price, quantities, images, etc from vendor data
filePATH = Application.ActiveWorkbook.Path & "\Dictionaries.xlsm"
On Error GoTo beeees
Workbooks("Dictionaries.xlsm").Close False
beeees:
Set openWB = Application.Workbooks.Open(filePATH)
Application.Run ("'Dictionaries.xlsm'!getPRICE")
Set ws = openWB.Worksheets("QTY")

'creates binding of dictionary and establishes the worksheets we will loop through in dictionaries workbook
Set dicPRICE = CreateObject("scripting.dictionary")
For x = Worksheets("QTY").Index + 1 To Worksheets("IMG").Index - 1

'this sends the vendors data per sheet to an array
With Sheets(x)
   ary1 = .Range("A1").CurrentRegion.Value2
End With

'adds the price from the vendor sheet to a dictionary
On Error GoTo err
    For i = 2 To UBound(ary1)
        
        If Not dicPRICE.exists(ary1(i, 1)) Then dicPRICE.Add ary1(i, 1), ary1(i, 17)
    
    Next i
Next x

'closes dictionary workbook
openWB.Close False

err:
Application.ScreenUpdating = True
End Sub

i changed the personal workbook to an addin and now these dictionaries no longer work in my main workbook. like if i need to lookup a price using this macro

'in my main workbook
Sub findPRICE()
Dim pnum As Variant

pnum = InputBox("Please Type in the part number")
If IsNumeric(pnum) Then pnum = CLng(pnum)
If dicPRICE.exists(pnum) = True Then
MsgBox dicPRICE(pnum)
Else
MsgBox "No pricing available for this part number"
End If


End Sub

i stepped through the new addin process and it does everything the personal macro workbook did but its shooting back a object error as if the dictionary was not created.
do i need to reference the addin like i do with a personal workbook? if so how do i do that with VBA?
i've tried public as a default because thats what i was already using and im unsure of how to pass a dictionary via function or sub otherwise, and it doesn't seem to have too many google results.

i get what you mean about the workbooks, but i guess the XLSB worked because i had made a reference to it., but i can't programmatically add a reference without first referencing Microsoft Visual Basic for Applications Extensibility or checking the Trust Access To Visual Basic Project in security settings. Both methods defeat the purpose of what i'm trying to accomplish here. Which i guess i have to just bite the bullet on and accept that this isn't happening with VBA.

 

[DataBlake]

It is possible to make data available between projects by stashing the values into those parts of the foreign workbook

This may be an alternative route, but the point of the arrays and dictionaries was to eliminate calculation time where storing/stashing these values on a worksheet would take ages because i'm working with 300k+ products all of which need price, quantity, image urls, color translations, listing numbers, titles, and shorthand codes for each product that are all carried in these dictionaries.
An example besides price lookup is to see if a product is already live on eBay
or check/remove duplicate listings using a combo of dictionaries.

its frustrating trying to help me with a huge project that i've been developing forever and ever without physically seeing everything, but this one issue is like the last puzzle piece you lost under the carpet and it can't be complete without a way for it to be portable. And for it to be portable it has to communicate values between a main program and public dictionaries.

 

[Greg Truby]

Blake, I think we can probably figure out some way to get you where you want to go. I'm thinking we gain some altitude and get away from the details and back out to a broader view. In broad strokes, what are we trying to accomplish? From what I can gather, you want the user -- while using whatever workbook they happen to be in, not any specific workbook -- to be able to quickly access certain data that is being stored in an Excel workbook. In the past you have stored this data in a dictionary that remains resident in memory via a hidden workbook.

Is the workbook that contains the dictionary object also the same workbook that contains the actually data written into the cells?
I'm interpreting what you've posted to mean that (a) your user interface is located in yet another workbook. What is driving this separation? And (b) your interface is a "one-at-a-time" kinda thing, i.e. the user calls up your userform and enters a product code and then your "OK" button fetches the data from a dictionary and returns it in the form of a message box?

Do I have this anywhere close to correct? Again, broad strokes, not too much detail (yet).

 

[DataBlake]

while using whatever workbook they happen to be in, not any specific workbook -- to be able to quickly access certain data that is being stored in an Excel workbook. In the past you have stored this data in a dictionary that remains resident in memory via a hidden workbook.

the data is not stored and changes daily (inventory levels, pricing, and even incorrect data being rectified by the vendors. It changes constantly). the dictionaries are loaded after a series of code is run to organize the data on a daily basis

Is the workbook that contains the dictionary object also the same workbook that contains the actually data written into the cells?

no i have a dictionary.xlsm which holds and/or obtains the data to be loaded into the actual dictionaries themselves which is stored in my personal XLSB
I download/call over 20 files of vendor data.

I'm interpreting what you've posted to mean that (a) your user interface is located in yet another workbook. What is driving this separation?

yes, i have a program i've made out of VBA and the UI looks like this

double clicking any of these cells performs a certain task using the data from dictionaries and other workbooks as a culmination of eCommerce tools.
the reason for this separation is that i, or whoever is operating the program, is dealing with 300k+ items. meaning 300k rows of data, at first i tried to combine everything but the workbook exploded from being too large of a file size. The reason its separated into the 3 parts that it is:

1: the main workbook is just to be a tool to complete whatever task (usually just removing out of stock listings and changing inventory levels). It initiates the loading of dictionaries upon opening it, after opening it the user should be able to use the dictionaries with the files generated or even separate workbooks so long as the same instance of excel is running.

2: the dictionary.xlsm is the "helper column" of the personal workbook dictionaries. it contains my image database and anything else that is unique to the company. custom color name translations (example: "chrome polished beadlock milled" would be too long for a listing title for just color. so its shorthand is "chrome". So this workbook just holds non vendor info/data.

3: the personal workbook which we've been over, holds important information to perform eCommerce tasks such as everything in the UI of my program and more.

this should also answer B as no it is not just a one off thing it is a complex system.
but the code you're referring to are the "lookup" buttons at the top right. I.E if someone were to double click "eBay Lookup" it would prompt an input box for a part number to see if its live on eBay or not.

edit: the folder tree in this post is also useful as it paints a picture of how it would work portably/where the files are.
eBay feed in ebay folder, export for the exported files to upload to ecommerce sites, update INV holds vendor data workbooks, and above all of that in the tree is the actual program and its working parts.

hope this helps.

 

[Legacy 456155]

I was caught up in your post for the simple reason that I am challenged simply trying to understand what you are trying to do. It's still not clear to me. It almost sounds as if you are using a workbook as a server so to speak. If that's the case, why not just use a separate instance of Excel? What I mean is this, the initial workbook opens the "server" in instance2. Any other workbooks that open from that point use instance2.Workbook(s) as the information server. This actually works well and is easy to do though if you are passing large amounts of data from instance2, you might run into bottlenecks because it is being passed cross-process. I've used multiple instances to pass off long running processes to the "server" which then notifies the client(s) when finished. I have not In any case ever used it to pass large amounts of information. If I'm even in the ballpark, let me know and I'll post an example.

 

[Greg Truby]

...the main workbook is just to be a tool to complete whatever task (usually just removing out of stock listings and changing inventory levels)...

I'm kinda starting to wonder the same thing as dataluver, i.e. did you do all of this in Excel because that is the programming toolkit you have at your disposal? In other words is this more or less a "stand alone" application? I'm asking because I was expecting you to do stuff like allow the user to select a range of cells and then drop in a list of prices or inventory levels or whatever. For example you have product codes in A2:A100 and the user can select that range and click a menu option in your application and make some selections on a userform and your program drops pricing in B2:B100 and Qty O/H in C2:C100.

But it sounds like your application's purpose is to post value changes (changing inventory levels) or delete records (removing out-of-stock listings) from whatever data source(s) is/are populating these Scripting.Dictionary objects and not like you're using this to provide functional enhancements to users who want to populate cells on worksheets with data coming from the dictionaries.

 

[DataBlake]

you are using a workbook as a server so to speak.

It IS a fuax database. (the dictionaries at least)

What I mean is this, the initial workbook opens the "server" in instance2. Any other workbooks that open from that point use instance2.Workbook(s) as the information server.

I work with a lot of data that constantly changes. a workbook itself grows in file size the more data it has in it.
In order to keep a "database" in excel it has to be rather small. The entire method to the madness was for this to be quick as if it were running in an actual database.
The array/dictionary combo is as fast as i've seen excel work.
If i spit out the data into another workbook and cross reference the workbook its going to take a very very very very long time to do the things my current method does in seconds.
I'm basically just yoinking data from a malgamation of workbooks and stuffing it in dictionaries. These data points never leave the dictionaries until enduser wants them to, thus not wasting computing time.
unless you're seeing it differently and can elaborate?

In other words is this more or less a "stand alone" application?

kind of? its definitely a fake database because i am not afforded database software at work. its also an app/program for an enduser using VBA as i don't have access to other programming languages either.
sometimes i feel like my IT department just hates me ?
but i make due

But it sounds like your application's purpose is to post value changes (changing inventory levels) or delete records (removing out-of-stock listings) from whatever data source(s) is/are populating these Scripting.Dictionary objects and not like you're using this to provide functional enhancements to users who want to populate cells on worksheets with data coming from the dictionaries.

yes my MAIN WORKBOOK is as you say but i want to ALSO provide functional enhancements to the user's own workbooks after the fact. So no matter what enduser's day starts with using "the program". once the program is used they can still use the dictionaries however they want with their own workbooks like you said: maybe populate a list using the dictionary key and value. endless possibilities,

I also want to update that i think i might have a solution but my IT department is once again blocking me from doing what i want.
I think a workbook can save a reference to the addin even if the addin is not CURRENTLY installed. I can install the addin manually and this seems to work. Unfortunately for some reason installing the addin programmatically gives me an access denied error on my work computers. I will be able to test this at home however in a couple weeks.