VBA Excel define Collection to be used in Function

Question

In Python i would do the following

jkk = {'Cadmium': 0.5, "Bly": 40}

def JKKCadmium(result):
    return result / jkk["Cadmium"] - 1

def JKKBly(result):
    return result / jkk["Bly"] - 1

I tried to do something similar in VBA

Option Explicit
Private JKK As New Collection
JKK.Add 0.5, "Cadmium"
JKK.Add 40, "Bly"

Function JKKCadmium(result As Double) As Double
    JKKCadmium = result / JKK("Cadmium") - 1
End Function

Function JKKBly(result As Double) As Double
    JKKBly = result / JKK("Bly") - 1
End Function

But that throws a compile error: "Invalid outside procedure". How can I make the VBA code work?

EDIT: Using dictionary instead still throws same error

Option Explicit
Private JKK As New Scripting.Dictionary
JKK.Add "Cadmium", 0.5
JKK.Add "Bly", 40

Function JKKCadmium(result As Double) As Double
    JKKCadmium = result / JKK("Cadmium") - 1
End Function

Function JKKBly(result As Double) As Double
    JKKBly = result / JKK("Bly") - 1
End Function

It looks like you are trying to use a Collection like a Dictionary. Have you looked into Dictionaries yet? https://stackoverflow.com/questions/915317/does-vba-have-dictionary-structure — braX, Dec 13 '20 at 13:04
The .add statements cannot be used at Module/Class level. They must be within a Sub/Function. — freeflow, Dec 13 '20 at 15:16

score 1 · Accepted Answer · answered Dec 13 '20 at 13:15

Probably better to use a class (and populate dicts within the class init). However, you can have your dictionaries as global public but then need an init to populate with values. You can then call within your funcs

Option Explicit

Public jkk As Scripting.Dictionary

Public Sub init()
    
    Set jkk = New Scripting.Dictionary
    jkk.Add "Cadmium", 0.5
    jkk.Add "Bly", 40
End Sub

Public Sub test()
      init
      Debug.Print JKKCadmium(2#)
      
End Sub

Public Function JKKCadmium(ByVal result As Double) As Double
    JKKCadmium = result / jkk("Cadmium") - 1
End Function

That did solve the error problem, but I might have approached it improperly. I will try to make a class instead. — Kim Petersen, Dec 13 '20 at 13:29

VBA Excel define Collection to be used in Function

1 Answers1