15

I'm working with VBA and need to save data in type key=>value to getting fastest; This data type help me cache responese text from http request, increase query speed. But I don't know what is the best way to do it? I need a data type same as php array with key=>value! Thank for help!

Davuz
  • 5,040
  • 13
  • 41
  • 61

2 Answers2

25

Have you looked at dictionary object?

It's available as part of the Microsoft Scripting Runtime. A clear example of how to add this is given by this SO answer.

Sub DictExample1()

Dim dict As Dictionary
Dim v As Variant

    'Create the dictionary          
    Set dict = New Dictionary

   'Add some (key, value) pairs
    dict.Add "John", 34
    dict.Add "Jane", 42
    dict.Add "Ted", 402

    'How many items do we have?
    Debug.Print "Number of items stored: " & dict.Count

    'We can retrieve an item based on the key
    Debug.Print "Ted is " & dict.Item("Ted") & " years old"


   'We can test whether an item exists
    Debug.Print "We have Jane's age: " & dict.Exists("Jane")
    Debug.Print "We have Zak's age " & dict.Exists("Zak")

    'We can update a value by replacing it
   dict.Item("Ted") = dict.Item("Ted") / 10

    Debug.Print "Ted's real age is: " & dict.Item("Ted")

   'We can add more items
    dict.Add "Carla", 23

   'And we can iterate through the complete dictionary
    For Each v In dict.Keys
        Debug.Print "Name: " & v & "Age: "; dict.Item(v)
    Next

End Sub

(Source: http://www.techbookreport.com/tutorials/vba_dictionary.html)

LondonRob
  • 73,083
  • 37
  • 144
  • 201
Irfarino
  • 522
  • 1
  • 8
  • 13
  • 2
    Please, don't do references to other sites. This is the place, where you can write the solution. – user61253764 Oct 13 '15 at 21:14
  • 14
    @PawelMiechowiecki: What's wrong with providing URL with extra information? Specially because to make this code work you have to enable the reference to "Microsoft Scripting Runtime" which step is described in the given URL... – Zé Carlos Apr 05 '17 at 16:30
  • 2
    It tells me invalid use of new Keyword for Set dict = New Dictionary – Zanidd Feb 23 '18 at 13:24
  • 3
    Including Microsoft Scripting Runtime in References will resolve this issue – Nagarajan Sathish Mar 04 '18 at 07:08
  • 1
    Since Microsoft Scripting Runtime needs to be enabled, does this mean it could cause problems when sharing your workbook with someone else who doesn't have it enabled, or is on a different system (such as a Mac)? – kojow7 Oct 05 '18 at 21:36
  • It's not available in MAC unfortunately – Si8 Mar 13 '19 at 14:10
  • @kojow7, yes - since the scripting runtime needs to be referenced there is potential that when sharing your workbook, someone does not have the same runtime version you referenced. There are 2 ways to add a reference though, and one of them can help with this. If using early binding to add your reference, the other person will need to have the same runtime version. If using late binding, the program can decide which runtime to use. – David Mays Jan 15 '21 at 20:00
1

Above answer will not work because of wrong syntax you need to add Scripting Keword before also enable Microsoft Sripting Run Time I tried it with and Without Sripting. before Dictionary in MS word(2016) and without dosen't work even if you have enabled Microsoft Scripting runtime

 Dim dict As Scripting.Dictionary
 Set dict = New Scripting.Dictionary
 dict.Add "John", 34
 dict.Add "Jane", 42
 dict.Add "Ted", 402

 Debug.Print "Number of items stored: " & dict.Count
 Debug.Print "Ted is " & dict.Item("Ted") & " years old"
abdullah
  • 86
  • 1
  • 7
  • This is incorrect, you don't have to add `Scripting.` for it to work. Also, Scripting is not a [Keyword](https://learn.microsoft.com/en-us/office/vba/language/reference/keywords-visual-basic-for-applications). The necessity to add the Microsoft Scripting Runtime to the references is mentioned, and even explained perfectly well in the linked post. – GWD Dec 10 '20 at 20:42
  • well i did that but it was throuwing an error invalid use of new Keyword – abdullah Dec 13 '20 at 07:01
  • Hello GWD have you Checked the code out in Ms. Word 2016 with an without Scripting . – abdullah Dec 13 '20 at 07:27
  • You are right, it seems the `Scripting.` is needed in Word because there exists another Dictionary out there. You are also right about the error! Just for your information, the Keywors the error is speaking of is not `Dictionary` but rather `New` :) – GWD Dec 13 '20 at 12:37
  • just a newbie in vba but when i tried it said that so i thought i should clear it for any one else thanks for the info about the error did not now about what the error was or why it was showing – abdullah Dec 13 '20 at 14:24
  • @GWD the method being used here utilizing Scripting.Dictionary is due to using late binding to reference the Microsoft Scripting Runtime version. Another method exists as well called early binding. If using late binding, scripting is required as shown here. Possibly you were able to not use Scripting.Dictionary, but were you also using early binding instead? – David Mays Jan 15 '21 at 20:03
  • @DavidMays no, the method used here is an example of [early binding](https://learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/early-late-binding/). In our discussion the confusion arose because, in Excel VBA, a Dictionary can be created with early binding using `Set dict = New Dictionary` while in Word VBA with early binding it is necessary to use `Set dict = New Scripting.Dictionary`. – GWD Jan 16 '21 at 01:24