2

I am learning about declaring arrays. It works when I declare it by giving an upper limit using following code:

Dim arrayA(5) as String

I check it by assigning a random value:

arrayA(0) = 1
MsgBox arrayA(0)

MsgBox responds by giving a value of 1.

However, my actual intention is to create a dynamic array that I defined as below:

Dim arrayA() as String

I test it in the same way

arrayA(0) = 1
MsgBox arrayA(0)

But this time it does not work and MsgBox pops up empty. Would someone tell me if I need to load some libraries to work with dynamic array?

braX
  • 11,506
  • 5
  • 20
  • 33
Lone
  • 129
  • 2
  • 16
  • The code you have posted should generate an error not an empty message box so something else is going on there (on error?) You are looking for *Dynamic Arrays* see https://stackoverflow.com/questions/8850984/populating-vba-dynamic-arrays – Alex K. Apr 01 '19 at 12:17
  • 2
    Possible duplicate of [Populating VBA dynamic arrays](https://stackoverflow.com/questions/8850984/populating-vba-dynamic-arrays) – FunThomas Apr 01 '19 at 12:18
  • I did not get any error and I have no error event established on this sub. I am using Access 2016, not sure if that makes a difference here. Anyways, thanks for sharing the link, I took a look and honestly, I have no clue how to use those additional functions which some people shared in the answers. My issue is solved by following comments below regarding usage of `redim`. – Lone Apr 01 '19 at 12:58

2 Answers2

4

Arrays in VBA need to be initialized before they are used.

You can initialize an array with a Redim statement:

Dim arrayA() as String
Dim i As Integer
i = 0
Redim ArrayA (0 To i)
arrayA(0) = "1" 'String
MsgBox arrayA(0)

Alternatively, there are functions that return an initialized array. In that case, Redim is not needed as the initialization happens in the external function. You do need to make sure you match type with the array being returned, though, and the overhead is the same or more.

Dim arrayA() as Variant
arrayA = Array(1)
MsgBox arrayA(0)
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • I watched youtube tutorials by Steve Bishop video # 34. VBA - Arrays (Programming In Access 2013). He was working with arrays without `redim` in the first instance. Anyways, thanks for your explanation, it works now! – Lone Apr 01 '19 at 12:42
  • @Lone I've added a bit more info on dynamic arrays without `Redim`, but iirc he does cover `redim` and the (dis)advantages of it in his series – Erik A Apr 01 '19 at 12:47
  • Alright. I believe plain usage of `redim` is good enough. I do not want to get into further complications with additional functions. – Lone Apr 01 '19 at 13:00
2

You can declare an array without a limit, but must redim the array to the desired limit prior to using it:

Dim myArray() As Long
Redim myArray(0)
myArray(0) = 0 'etc...

So, you cannot use a "dynamic" array in VBA like this.

The best reference I've ever known for arrays (and much other VB/A related information), comes from the late Chip Pearson: http://www.cpearson.com/Excel/VBAArrays.htm

jleach
  • 7,410
  • 3
  • 33
  • 60
  • That's a great reference. Thanks for sharing! – Lone Apr 01 '19 at 12:43
  • 2
    Chip's arrays module is drop-in for any VBA host application and was a standard item for any project when I was doing a lot of Access development. His site is a trove of (extremely complete) information in a great many areas. I was an Access MVP for 5 years and can still go to the Errors page of his and learn a thing or two :) Bookmark highly recommended (and hopefully with his passing a year or so ago, the site will continue to remain...) – jleach Apr 01 '19 at 14:08