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

 

[Alex20850]

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

Sorry, I am not familiar with VBA. What would it look like?

 

[StephenCrump]

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

In a new workbook, create a range name

MyTable: =A2:B8

Book1
	A	B
1	HASC	FIPS
2	US.AK.AE	31011
3	US.AK.AN	27129
4	US.AK.AW	51650
5	US.AK.BE	55135
6	US.AK.BR	54081
7	US.AK.DE	17031
8	US.AK.DI	36049
Sheet1

In a code module (and assuming Sheet1 is the code name for the worksheet where you've stored MyTable):

Function GetFromMaster(s As String, Col As Long) As Variant

    GetFromMaster = Application.VLookup(s, Sheet1.Range("MyTable"), Col, 0)

End Function

Save the workbook as an Excel Add-in, e.g. assuming you have Excel 2007 or more recent, as say, MyAddIn.xlam

Use File/Options/Add-ins/Manage Excel Add-ins/Go ...

to instal your Add-in.

Then on opening Excel, you should be able to call your GetFromMaster function from any workbook.