Dynamically Dimensioning A VBA Array?

by

You can use a dynamic array when you don’t know the number of values it will contain until run-time:

Dim Zombies() As Integer
ReDim Zombies(NumberOfZombies)

Or you could do everything with one statement if you’re creating an array that’s local to a procedure:

ReDim Zombies(NumberOfZombies) As Integer

Fixed-size arrays require the number of elements contained to be known at compile-time. This is why you can’t use a variable to set the size of the array—by definition, the values of a variable are variable and only known at run-time.

You could use a constant if you knew the value of the variable was not going to change:

Const NumberOfZombies = 2000

but there’s no way to cast between constants and variables. They have distinctly different meanings.

More Related Contents:

  1. VBA array sort function?
  2. Excel-VBA – Insert new first column in datafield array without loops or API calls
  3. Creating an Array from a Range in VBA
  4. Expanding column cells for each column cell
  5. How to find if an array contains a string [duplicate]
  6. Check if a value is in an array or not with Excel VBA
  7. How to search for string in an array
  8. Sorting a multidimensionnal array in VBA
  9. Deleting Elements in an Array if Element is a Certain value VBA
  10. Is it possible to fill an array with row numbers which match a certain criteria without looping?
  11. How to automatically input an array formula as string with more than 255 characters in length into an excel cell using VBA?
  12. Return Index of an Element in an Array Excel VBA
  13. Excel VBA: Range to String Array in 1 step
  14. How to initialize a multidimensional array variable in vba for excel
  15. VBA using ubound on a multidimensional array
  16. Turn Excel range into VBA string
  17. Get values from union of non-contiguous ranges into array with VBA with a simple command (no loops)
  18. Why am I having issues assigning a Range to an Array of Variants
  19. Subscript out of range error with an array – no idea why?
  20. VBA Excel large data manipulation taking forever
  21. Performance difference between looping range vs looping array
  22. how to combine duplicate rows and sum the values 3 column in excel
  23. Populating VBA dynamic arrays
  24. Create Chart from Array data and not range
  25. VBA – Returning array from Property Get
  26. How do I Merge two Arrays in VBA?
  27. Excel VBA Quickest way to sort an array of numbers in descending order?
  28. Simple VBA array join not working
  29. Excel VBA – How to Redim a 2D array?
  30. ReDim Preserve “Subscript Out of Range”

Leave a Comment