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

Alex20850

Board Regular
Joined
Mar 9, 2010
Messages
102
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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 9, 2010
Messages
102
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 9, 2010
Messages
102

ADVERTISEMENT

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

Well-known Member
Joined
May 16, 2017
Messages
771
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

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Mar 9, 2010
Messages
102
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
 
Last edited:

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,797
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,579
Messages
5,523,705
Members
409,531
Latest member
Lmfacc

This Week's Hot Topics

Top