Question about understanding Arrays

[dan7055]

I was wondering if there was a way to have an array that only has the quantity of indexes necessary, when you are not sure how many indexes it will need.

It's my understanding that you have to declare how many indexes an array will have before using it, however in this code (which I didn't write) it seems that the array is not declared at all. Can somebody explain what is going on in this code with the variable "ExcelFilePath"?

Sub Compress_Raw_Data()




ExcelFilePath = Application.GetOpenFilename(FileFilter:="CSV (Comma delimited) (*.csv), *.csv", _
            Title:="Select a file or files", _
            MultiSelect:=True)
    If VarType(ExcelFilePath) = vbBoolean Then
        MsgBox (" You have not selected any file")
        Exit Sub
    End If




For fnum = LBound(ExcelFilePath) To UBound(ExcelFilePath)


'PURPOSE: Find & Replace text/values throughout entire workbook


    Workbooks.Open Filename:=ExcelFilePath(fnum)
    
Dim sht As Worksheet


For Each sht In ActiveWorkbook.Worksheets


  sht.Columns("A").NumberFormat = "mm/dd/yyyy  hh:mm:ss.0"
  
Next sht
    
ActiveWorkbook.Close True


Next fnum




End Sub

 

[Eric W]

A Variant can hold any kind of variable, even an array. That's usually pretty inefficient though. It's usually best to specifically define a variable in the Dim statement as what it really is. That way VBA doesn't have to figure out what's in it every time it's referenced.

This:

Dim MyArray as variant
MyArray = Array(0,1,2,3,4,5)

is a shorthand way of initializing an array, and is fine for small programs where efficiency is not at a premium. The values are assigned as follows:
MyArray(0) = 0
MyArray(1) = 1
. . .
MyArray(5) = 5

You could also do this:

Dim MyArray(0 to 5) as Integer, i as Integer

For i = LBound(MyArray) to UBound(MyArray)
    MyArray(i) = i
Next i

That pretty much illustrates the concept, but you can see that the Array syntax is quicker to type for small arrays, and if the values you're assigning aren't consecutive, or are text, the For loop might not work.

 

[dan7055]

A Variant can hold any kind of variable, even an array. That's usually pretty inefficient though. It's usually best to specifically define a variable in the Dim statement as what it really is. That way VBA doesn't have to figure out what's in it every time it's referenced.

This:

Dim MyArray as variant
MyArray = Array(0,1,2,3,4,5)

is a shorthand way of initializing an array, and is fine for small programs where efficiency is not at a premium. The values are assigned as follows:
MyArray(0) = 0
MyArray(1) = 1
. . .
MyArray(5) = 5

You could also do this:

Dim MyArray(0 to 5) as Integer, i as Integer

For i = LBound(MyArray) to UBound(MyArray)
    MyArray(i) = i
Next i

That pretty much illustrates the concept, but you can see that the Array syntax is quicker to type for small arrays, and if the values you're assigning aren't consecutive, or are text, the For loop might not work.

I have been using arrays for a while and am already familiar with how to set the values of an array using a loop and a second variable. What I don't know how to do is define an array when you don't know how many indexes it will need and then have it only have the amount of indexes it actually ended up requiring in the code.

 

[HackSlash]

Why are some arrays defined with a () after them and some aren't? What's the difference?

That is an "undimensioned" array. See my first post about how excel handles an array when the dimensions are omitted. I prefer to start with the minimum you expect to use.

Let's say that in your example you think that the user will select about 3 files every time.

Dim Example(0 to 2) as String

Now if they only select one file then you have 2 empty strings.

Compare that to:

Dim Example() as String

In this scenario it doesn't matter if the user is selection 1 or a million, the array will need to be redimensioned as I described above.

 

[HackSlash]

For more information please read the MS article on Arrays for VBA:

https://msdn.microsoft.com/EN-US/library/office/gg264224.aspx

specifically the section called "Declaring a Dynamic Array"

 

[Eric W]

From post #4:

Dim MyArray() as Long, x as integer

x = 5
Redim MyArray(x)

That's best used as soon as you know what the size is. If you want to dynamically change the size of the array as you go, you can do this:

Dim MyArray()

Redim MyArray(1)

MyArray(1) = 1

For i = 2 to Rnd() * 10
    Redim Preserve MyArray(Ubound(MyArray)+1)
    MyArray(Ubound(MyArray)) = i ^ 2
Next i

As Hackslash mentioned, that's horribly inefficient, but it illustrates the concept. Frankly, with the amount of RAM available on today's computers, defining an array with extra space is not an issue unless you're creating massive arrays.

 

[dan7055]

For more information please read the MS article on Arrays for VBA:

https://msdn.microsoft.com/EN-US/library/office/gg264224.aspx

specifically the section called "Declaring a Dynamic Array"

I read the article, but how can I declare a dynamic 2 dimensional array?

 

[HackSlash]

I read the article, but how can I declare a dynamic 2 dimensional array?

Declare like a normal array. Redim will define the dimensions on the first pass.

Keep in mind that you cannot change the number of dimensions with "Redim Preserve" without losing all your data so if you make a 2D array that you want to expand just keep it 2D from the begining.

When you first redim the size you make it 2D:

[COLOR=#574123]Redim Preserve MyArray(0 to Dimension1size, 0 to Dimension2size)[/COLOR]

and you resize it the same way. Just be sure to resize before you use more indexes or you will get "subset out of range"