0

In VBA, I would like to create a 2D array whose values can't be known at compile time.

I tried this:

Dim symbols As Object
Set symbols = CreateObject("System.Collections.ArrayList")

Dim dictionary As Object
Set dictionary = CreateObject("Scripting.Dictionary")

Dim entries As Integer
entries = dictionary.Count

Dim sheet(symbols.Count, entries) As String

Only entries is highlighted.

I thought it came from the way I counted the dictionary size and that Excel highlighted the bad part of the code) so I tried entries = UBound(dictionary.keys)

I don't get why symbols.Count isn't highlighted since it won't be known at compile time. Why is that?

How am I supposed to create an array without knowing its size?

Nocxy
  • 111
  • 7
  • `Dim` first with no dimensions, then use `ReDim` at runtime. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/redim-statement#:~:text=The%20ReDim%20statement%20is%20used%20to%20size%20or%20resize%20a%20dynamic%20array%20that%20has%20already%20been%20formally%20declared%20by%20using%20a%20Private%2C%20Public%2C%20or%20Dim%20statement%20with%20empty%20parentheses%20(without%20dimension%20subscripts). – Tim Williams Apr 13 '23 at 21:46
  • Thank you. Will I get errors at runtime if I don't specify that it's a 2D array in the ``Dim`` part? – Nocxy Apr 13 '23 at 21:51
  • https://stackoverflow.com/a/13184027/478884 – Tim Williams Apr 13 '23 at 21:52

0 Answers0