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

 

[Norie]

Application.GetOpenFilename returns an 1 dimensional array to ExcelFilePath if multiple files are selected, single string if 1 file is selected and a boolean value if Cancel is pressed.

If you were to declare ExcelFilePath it would probably be best declared as Variant so it can handle all 3 of those data types.

 

[dan7055]

Application.GetOpenFilename returns an 1 dimensional array to ExcelFilePath if multiple files are selected, single string if 1 file is selected and a boolean value if Cancel is pressed.

If you were to declare ExcelFilePath it would probably be best declared as Variant so it can handle all 3 of those data types.

Yes, but if it's a one dimension array, why don't you have to declare how many indexes it has? Most internet articles on using arrays state that arrays must have the quantity of indexes declared before-hand. So how does that work?

 

[Norie]

Not sure I follow, the posted code doesn't declare anything.

Also, the only array the function Application.GetOpenFileName will ever return is a 1 dimensional array.

 

[Eric W]

VBA has several methods of generating arrays of variable size. GetOpenFilename is just one. Here are a few other ways:

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

You can define the elements of the array as you wish.

Dim MyArray as variant, Str1 as String
Str1 = "Ohio, Utah, Iowa, Alaska, Hawaii"
MyArray = Split(Str1, ",")

The Split function splits a string into a zero-based array whose size depends on what the string is. In this case:
MyArray(0) = "Ohio"
MyArray(1) = "Utah"
MyArray(2) = "Iowa"
etc.
You can use UBound(MyArray) to find out the upper bound.

Dim MyArray() as Long, x as integer

x = 5
Redim MyArray(x)

This method allows you to define an array of Longs, but with no dimensions. You dimension it within the code using the Redim statement.

Depending on what you want to do, there are several ways of getting arrays of variable size. Check the documentation for these various commands.

Hope this helps!

 

[HackSlash]

Depends on the programming language. In a lot of languages you have to define the size of the array and you can't change it. In some languages there is a built-in handler to re-dimension an array. In VBA it's called "Redim". If you don't declare the number of indexes in an array it's just a variant. Once you start putting stuff in it VBA will internally use it's "Redim" function to make a new array every time you add a new dimension. It's horribly inefficient.

Here is what the redim is actually doing:

1. Create new array with enough dimensions
2. Copy the old array in to the new array
3. Add the new data
4. Delete the old array
5. Rename the new array to the old array name

This is done for your convenience but it's not recommended in large programs where performance is important.

 

[dan7055]

Application.GetOpenFilename returns an 1 dimensional array to ExcelFilePath if multiple files are selected, single string if 1 file is selected and a boolean value if Cancel is pressed.

If you were to declare ExcelFilePath it would probably be best declared as Variant so it can handle all 3 of those data types.

What I'm asking is why you don't have to declare how many indexes you have in the variable ExcelFilePath. For example, normally you'd have to do this:

dim ExcelFilePath(1 to 500)

or

dim ExcelFilePath(1 to 10)

If you don't end up using all 500 of the indexes, the you just have wasted space. You also can't use the function ubound(excelfilepath) to determine how many indexes are actually filled because it will just return the declared index quantity of 500.

Any internet article you read that explains Arrays in VBA will state that the number of indexes must be declared before using an array... However, in my example code, this is somehow avoided. This is why I'm confused.

 

[Norie]

The no of indexes/items returned by Application.GetOpenFilename can not be determined because it's based on how many files the user selects.

 

[dan7055]

VBA has several methods of generating arrays of variable size. GetOpenFilename is just one. Here are a few other ways:

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

You can define the elements of the array as you wish.

Dim MyArray as variant, Str1 as String
Str1 = "Ohio, Utah, Iowa, Alaska, Hawaii"
MyArray = Split(Str1, ",")

The Split function splits a string into a zero-based array whose size depends on what the string is. In this case:
MyArray(0) = "Ohio"
MyArray(1) = "Utah"
MyArray(2) = "Iowa"
etc.
You can use UBound(MyArray) to find out the upper bound.

Dim MyArray() as Long, x as integer

x = 5
Redim MyArray(x)

This method allows you to define an array of Longs, but with no dimensions. You dimension it within the code using the Redim statement.

Depending on what you want to do, there are several ways of getting arrays of variable size. Check the documentation for these various commands.

Hope this helps!

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

 

[dan7055]

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

What exactly does this do?