Syntax Error when attempting to create a multidimensional array in VBA

Question

I am attempting to create a VBA script for Microsoft Access that pulls titles (such as Mr., Mrs.,Dr.,etc.) from a name field and then return a cleaned specific version of that title. I tried to make a multidimensional array with the title in one column and the cleaned title in another column but when I attempt to compile the script, I get a syntax error. I know the syntax error is because of the multidimensional array as I based this off a previous script that used to just find the old titles and it worked fine. What am I missing that's causing this to be a syntax error? Did I not call the multidimensional array correctly? Admittedly I am new to VBA scripts and rusty in programming in general so forgive me if I'm missing something simple.

    Public Function findTitles(inName) As String
    ' Returns cleaned up titles
    Dim strName As String
    Dim strTitle As String
    Dim Titles As Variant
    Titles = Array(Array("Ms", "Ms."), Array("Miss ", "Miss"), Array("Mrs", "Mrs."), Array("Mr", "Mr."), Array("Master", "Master"), Array("Rev", "Rev."), Array("Reverend", "Reverend"), Array("Fr", "Fr."),Array("Father", "Father"), Array("Dr", "Dr."), Array("Doctor", "Doctor"), Array("Atty", "Atty."), Array("Attorney", "Attorney"),Array("Prof", "Prof."), Array("Professor", "Professor"), Array("Hon", "Hon."), Array("Honorable", "Honorable"), Array("Pres", "Pres."), Array("President",  "President"), Array("Gov", "Gov."), Array("Governor", "Governor"))

    Dim I
    I = 0
    strTitle = ""

    'Compare input to list of Titles
    Do
        If inName Like Titles(I, 0) & " *" Then
            strTitle = Titles(I, 1)
        Else
            I = I + 1
        End If
    Loop While (strTitle = "" And I < UBound(Titles))

    cleanTitles = strTitle
End Function

Blackhawk · Accepted Answer · 2014-10-01 21:13:12Z

4

The problem is that your array is not multi-dimensional, but nested. The error you are likely getting is not a syntax error, but instead a "Subscript out of range." error. You need to change your array statments like so:

Titles(I, 0)

Should be

Titles(I)(0)

Additionally on your last line you have:

cleanTitles = strTitle

I think you meant:

findTitles = strTitle

edited Oct 1, 2014 at 21:13

answered Oct 1, 2014 at 20:48

Blackhawk

6,1804 gold badges31 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TKESuperDave Over a year ago

Thank you. You were correct the array statement was wrong.

Collectives™ on Stack Overflow

Syntax Error when attempting to create a multidimensional array in VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related