I want to make a custom function that would replace/translate words (4000 words approx.). I managed to create and fill the dictionary inside the function, but that seems highly redundant, creating the dictionary each time the function is called. So I wanted to create the dictionary outside the function and then use it inside the function, like so:
Option Explicit
Sub make_dict()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
With dict
.Add "house", "Haus"
.Add "dog", "Hund"
.Add "cat", "Katze"
End With
End Sub
Function translate(ByRef a_word As String) As String
'~~> how to reference dictionary here ?
If Len(a_word) = 0 Then
translate = "#NA"
Exit Function
End If
Dim found As Boolean
found = False
For Each k In dict.keys
key = k
If key = a_word Then
translate = dRL.Item(k)
found = True
Exit Function
End If
Next k
If found = False Then
translate = "#NA"
End If
End Function
However, I cant find the right way of referencing the dict, when calling the function as
=translate(A1)
with the string "dog" in cell A1
You could use a static variable:
Option Explicit
Sub make_dict(dict As Object)
Debug.Print "called"
Set dict = CreateObject("Scripting.Dictionary")
With dict
.Add "house", "Haus"
.Add "dog", "Hund"
.Add "cat", "Katze"
End With
End Sub
Function translate(ByRef a_word As String) As String
Static dict As Object
If dict Is Nothing Then make_dict dict
'~~> how to reference dictionary here ?
If Len(a_word) = 0 Then
translate = "#NA"
Exit Function
End If
Dim found As Boolean
found = False
Dim k
For Each k In dict.keys
If k = a_word Then
translate = dict.Item(k)
found = True
Exit Function
End If
Next k
If found = False Then
translate = "#NA"
End If
End Function