How to measure or manage .add lines in Scripting.dictionary?

[Alex20850]

Is there a way to determine how many .add lines can be active?
Is there a way to increase the number of .add lines?

Function BackandForth(ByVal str As String) As String
With CreateObject("Scripting.Dictionary")
.Add "Alaska", "AK"
.Add “AK”,”Alaska”
If .exists(str) Then BackandForth = .Item(str)
End With
End Function

 

[StephenCrump]

Is there a way to determine how many .add lines can be active?
Is there a way to increase the number of .add lines?

Can you provide a bit more detail about what you are trying to do?

I don't understand either question.

Nor why your function creates a new dictionary object?

 

[Alex20850]

Can you provide a bit more detail about what you are trying to do?

I don't understand either question.

Nor why your function creates a new dictionary object?

I work with a lot of data with state fields - some state abbreviations, some with state names.

Depending on the report I am doing, I may need to change all the state abbreviations to state names or
all the state names to state abbreviations.

The VBA I showed is just a couple of .add lines.
The complete function code has over 100 .add lines for state/abbreviations and state/abbreviation names.
It works fine now, but I want to expand it. To do the same type of function using United States county codes/names,
there would over 6,000 .add lines.

 

[StephenCrump]

Wouldn't it be better to store your tables in Excel, rather than hard-coding them in VBA? Much easier then to inspect, amend, add/delete, sort etc.

Then if you still want to use a dictionary approach, a simple loop will be sufficient to populate it.

You could also populate the dictionary once, e.g. on Workbook_Open (or if the lookup tables changed) rather than every time your function is called.

 

[Alex20850]

I wanted to use as function since it would be used in a number of workbooks.
Also, the data is static - state and other abbreviations don't change.

 

[rlv01]

I don't think there is any limit other than PC memory on the number of keys you can add to a scripting dictionary so you might be ok with 6000 elements. I do think it is wildly inefficient to have a UDF like like BackandForth add 6000+ keys and items each time you want to call it to flip between abbreviation and full name for a single state or county.

 

[StephenCrump]

I wanted to use as function since it would be used in a number of workbooks.

Sounds like an Add-in would be perfect: store the lookup table in a worksheet in the Add-In, and use a simple Index/Match approach in your Function?

 

[Alex20850]

Sounds perfect. I found this https://www.mrexcel.com/forum/excel-questions/74386-using-add-worksheet-function-vba.html
and will look at it more closer later.

Thanks,
Alex

 

[Alex20850]

How to Have Formula Get and KEEP Data with Vlookup

I am looking for a way to have access to a MasterLookup tab every time I open a spreadsheet, but would not be in a template.
It shouldn't be a link, since the files could be moved.
There would be one tab in the MasterLookup tab with the A column in alphabetical order and one or more additional columns.
I would use Vlookup on a value in the new spreadsheet and bring back a value in the MasterLookup.
I would like the user defined function in the following situation to work something like the following:
In new spreadsheet, A2 is US.AK.AN. The command in B2 would be GetFromMaster(A2,2) and would return 27129 (see below)
In MasterLookup:

HASC 'FIPS
US.AK.AE '31011
US.AK.AN '27129
US.AK.AW '51650
US.AK.BE '55135
US.AK.BR '54081
US.AK.DE '17031
US.AK.DI '36049
​

 

[StephenCrump]

Re: How to Have Formula Get and KEEP Data with Vlookup

I am looking for a way to have access to a MasterLookup tab every time I open a spreadsheet, but would not be in a template.
It shouldn't be a link, since the files could be moved.

One solution would be the Add-in approach from Post#7:

- Store the table in a worksheet in the Add-in.

- Define your GetFromMaster Function in the Add-in