Personal Workbook Macro Punlic Dictionary Set Up [vba]

[DataBlake]

I want to create some dictionaries that i can access in any workbook.
I.E a list of colors as the key and a list of shortened versions of that color.

Gloss Black White Trim = key
Black = Value

i have a sheet with these values that i would like to establish as a disctionary
A: will this work if i set this vba code publicly dim the dictionary object in my personal workbook?
B: i'm pretty new to setting up dictionaries so how can i convert a sheet *key in Column A, value in Column B* to a dictionary effectively?
C: if possible is this something i can keep updated?

the plan is i open a vendor's data sheet and they have weird color names or other similar problem and instead of doing a 60,000 item vlookup i could just use arrays and dictionaries to change values in the sheet with a vba sub

 

[DataBlake]

If this is ultimately going to be used by other people, I'd put all the relevant code in the workbook it relates to.

i would in the end replace the active sheet in the XLSB with an actual file path to a different workbook so that i'm not updating the dictionaries on every file i have to open like i currently am.
otherwise i wouldnt really need this method. so like a:

Workbook1.XLSM
Dictionary.XLSX
Personal.XLSB

I open Workbook1, it grabs the info from Dictionary.XLSX and creates dictionaries from respective sheets.
Then i can run various codes using those dictionaries as complicated array matches/lookups/counts/etc
and sharing with other users is going to be via shared folder/drive so the directory and actual file will be the same

 

[Fluff]

In that case put all the code in the workbook it's needed in.
If you want to keep them in separate workbooks have a look at this thread https://www.mrexcel.com/forum/excel...ss-public-constant-another-workbooks-vba.html

 

[DataBlake]

have a look at this thread https://www.mrexcel.com/forum/excel...ss-public-constant-another-workbooks-vba.html

thats a really good thread thank you.

 

[Fluff]

You're welcome

 

[DataBlake]

using the code from post #7 and adding a reference to the XLSB VBAProject (which i've named Dictionaries) it works perfectly
same thing if i pass my dictionary object using a public property in the ThisWorkbook module of the XLSB

I couldn't be more elated about this working.