CODEWITHSUNDEEP
arraysexcelvba
yuro
yuro

Reputation: 2209

Returning value from Array in VBA

I have defined a message function to put different messages in it. That means when I call this function in a form, then just the corresponding chosen message has to display on the screen. Here my example:

'Thats the message sub
Public Sub MsgString(txtNo As Byte)
    MsgBox MsgTxt(txtNo)
End Sub

'Thats the message function to return the corresponding value, which is chosen
 Private Function MsgTxt(txtNo As Byte) As String
     Dim arrMsgTxt() As Variant
     arrMsgTxt = Array("error message number one", "error message number two", "error message number three")

     For txtNo = LBound(arrMsgTxt) To UBound(arrMsgTxt)
            MsgTxt = arrMsgTxt(txtNo)
            Exit Function
     Next txtNo
 End Function

Is there a better way to solve it? Currently, I need to exit the function after the instantiated value.

Upvotes: 0

Views: 54

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

The usual way to to pass an index to the UDF. That way, you don't need a loop:

Public Function TellMe(indx as Long) as String
    ary = Array("Mike", "James", "Paul", "William")
    TellMe = ary(indx)
End Function

Sub MAIN()
    MsgBox TellMe(2)
End Sub

Remember, the default indexing is zero-based............so expect "Paul"

Upvotes: 2

Related Questions