Returning value from Array in VBA

Question

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.

Why would you need to loop through the array of error messages if you know (presumably by the value of txtNo) which one of the messages you want to use? You could drop the For and Next lines, along with the Exit Function and have no change to the functionality — Dave
– Dave, Commented May 18, 2016 at 11:57
@Dave Yes, the variable txtNo contains the number in the array. Do you have an idea for a better solution? — yuro
– yuro, Commented May 18, 2016 at 12:01

Gary's Student · Accepted Answer · 2016-05-18 12:07:06Z

2

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"

edited May 18, 2016 at 12:07

answered May 18, 2016 at 12:01

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

2 Comments

yuro Over a year ago

Thanks. I had this defined before and had a strange error message, however, now it works :)

Gary's Student Over a year ago

@yuro Thank you for the feedback!

Collectives™ on Stack Overflow

Returning value from Array in VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related