Defining a Literal 2D String Array in VBA

Question

I'm trying to construct a utility function to prompt the user for an arbitrary file via the standard Windows file dialog.

I would like to pass the list of filetype filters as a two dimensional array of strings, where the first element of each subarray is the filetype description and the second element is the filetype filter.

Below is my function:

'' GetFile  -  Lee Mac
''
'' Prompts the user to select a file using a standard Windows file dialog
''
'' msg - [str] Dialog title
'' ini - [str] Initial filename/filepath
'' flt - [arr] Array of filetype filters
''
Function GetFile(Optional strMsg As String = "Select File", Optional strIni As String = vbNullString, Optional arrFlt) As String
    Dim dia As FileDialog
    Set dia = Application.FileDialog(msoFileDialogFilePicker)
    With dia
        .InitialFileName = strIni
        .AllowMultiSelect = False
        .Title = strMsg
        .Filters.Clear
        If IsMissing(arrFlt) Then
            .Filters.Add "All Files", "*.*"
        Else
            Dim i As Integer
            For i = 0 To UBound(arrFlt, 1)
                .Filters.Add arrFlt(i, 0), arrFlt(i, 1)
            Next i
        End If
        If .show Then
            GetFile = .selecteditems.Item(1)
        End If
    End With
End Function

This works, however, when supplying the filetype filter argument to the function, I find myself having to do something like this:

Function test()
    Dim arr(1, 1) As String
    arr(0, 0) = "Excel Files"
    arr(0, 1) = "*.xls;*.xlsx"
    arr(1, 0) = "Text Files"
    arr(1, 1) = "*.txt"

    GetFile , , arr
End Function

I've also tried the following but receive 'Subscript out of range':

Dim arr() As Variant
arr = Array(Array("Excel Files", "*.xls;*.xlsx"), Array("Text Files", "*.txt"))

Is there a better way to define a literal 2D string array that I'm missing?

Many thanks in advance for your advice & feedback.

Have deleted my answer which only worked in Excel. Apologies. — S Meaden
– S Meaden, Commented Jan 17, 2018 at 20:16
@SMeaden no problem - thank you for your time & contribution! — Lee Mac
– Lee Mac, Commented Jan 17, 2018 at 20:48

Krish · Accepted Answer · 2018-01-18 11:42:13Z

Because you commented that you could edit the getFile function, you should consider this approach. Using an Array might be a simple and straightforward idea but if your applications is complex enough, there is a chance your Array initialisations could become clumsy.

Below approach is just an introduction to classes and maybe to design pattern. Have a look.

Public Function test()

    Dim fe As New FileExtensions 'initialise your file extension class

    'Add filters
    fe.AddFilter "All Files", "*.*" 'add here or in class defaults
    fe.AddFilter "Excel Files", "*.xls; *.xlsx"
    fe.AddFilter "Text Files", "*.txt"

    GetFile , , fe
End Function

Function GetFile(Optional strMsg As String = "Select File", Optional strIni As String = vbNullString, Optional arrFlt) As String
    Dim dia As Object
    Set dia = Application.FileDialog(3)
    With dia
        .InitialFileName = strIni
        .AllowMultiSelect = False
        .Title = strMsg
        .filters.Clear

        'Simply retrieve the filters from extension class
        If Not IsMissing(arrFlt) Then
            Dim i As Long
            For i = 0 To arrFlt.getCount - 1
                .filters.ADD arrFlt.getDescription(i), arrFlt.getFilter(i)
            Next i
        End If
        If .Show Then
            GetFile = .selecteditems.item(1)
        End If
    End With
End Function

and a FileExtensions class

Option Compare Database
Option Explicit

Private Type FileExtension
    tDescription As String
    tFilter As String
End Type
Private Holder() As FileExtension

Public Sub class_initialize()
    ReDim Holder(0) ' or if you want to add default filters
End Sub

Public Sub AddFilter(Description As String, Filter As String)

    ReDim Preserve Holder(UBound(Holder) + 1)
    Holder(UBound(Holder) - 1).tDescription = Description
    Holder(UBound(Holder) - 1).tFilter = Filter

End Sub

Public Function getCount() As Long
    getCount = UBound(Holder)
End Function

Public Function getDescription(index As Long) As String
    getDescription = Holder(index).tDescription
End Function

Public Function getFilter(index As Long) As String
    getFilter = Holder(index).tFilter
End Function

Wow - thank you for this example! I have not used classes previously, so this is a great introduction.

Gustav · Accepted Answer · 2018-01-17 13:43:52Z

0

Your last method will work:

Dim arr() As Variant
arr = Array(Array("Excel Files", "*.xls;*.xlsx"), Array("Text Files", "*.txt"))

Your error must be caused by something else.

answered Jan 17, 2018 at 13:43

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

3 Comments

Lee Mac Over a year ago

Thanks - this does work in defining an array, however, not a two-dimensional string array, but rather an array of arrays... therefore rather than accessing the elements using arr(0,0), I have to access them using arr(0)(0)...

Gustav Over a year ago

That is true. But it should fit the purpose.

Lee Mac Over a year ago

Indeed - I could modify my GetFile function above to expect an array using this structure, but I thought there may be a more "correct" way in which to define a literal 2D string array, as it seemed like it should be a relatively straightforward exercise.

Alex K. · Accepted Answer · 2018-01-17 14:02:03Z

0

Use another utility function to make the array then you can:

GetFile , , StrsTo2d("Excel Files", "*.xls;*.xlsx")
GetFile , , StrsTo2d("Excel Files", "*.xls;*.xlsx", "Text Files", "*.txt")
GetFile , , StrsTo2d("Excel Files", "*.xls;*.xlsx", "Text Files", "*.txt", "FooFile", "*.foo")

Function StrsTo2d(ParamArray args() As Variant) As String()
    Dim     i As Long
    Dim num As Long: num = (UBound(args) - 1) / 2
    ReDim   out(num, 1) As String

    For i = 0 To num
        out(i, 0) = args(i * 2)
        out(i, 1) = args(i * 2 + 1)
    Next

    StrsTo2d = out
End Function

answered Jan 17, 2018 at 14:02

Alex K.

177k32 gold badges276 silver badges299 bronze badges

2 Comments

Lee Mac Over a year ago

Thanks Alex, this is cleaner solution than my current approach, though of course still populating an empty array as opposed to defining a literal array... Perhaps this is not possible in Access VBA.

Alex K. Over a year ago

Sadly vba/s/6 do not support variable initialization of any type for any type.

Collectives™ on Stack Overflow

Defining a Literal 2D String Array in VBA

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related