vba arrays and dynamically adding message errors to a msgbox

Question

I am trying to make an array that dynamically holds error messages

the following code works but is not dynamic in any way:

Dim Errors(1 To 4) As String

Errors(1) = "-Customer is blank on setup sheet"
Errors(2) = "-From is blank on setup sheet"
Errors(3) = "-Attention is blank on setup sheet"
Errors(4) = "-Job Number is blank on setup sheet"

For i = LBound(Errors) To UBound(Errors)
    msg = msg & Errors(i) & vbNewLine
Next i
MsgBox vbNewLine & msg, , "Missing Info"

how can i assign a variable to the variable name?

i am trying to do something like this:

num = 0
Dim Errors(1 To num) As String

I am hiding my errors inside if else statements and calling my loop at the end to display the errors. I am unable to use Dim Errors(1 to num) As String because an error is thrown saying "constant expression is required"

Jim Simson · Accepted Answer · 2016-07-06 02:01:56Z

2

Alilland, You're oh so close. What you need to do is use ReDim rather than Dim. Using ReDim will allow you to use a non-constant value to define the limits of your array. Thus,

num = 4
ReDim Errors(1 To num) As String
Errors(1) = "-Customer is blank on setup sheet"
Errors(2) = "-From is blank on setup sheet"
Errors(3) = "-Attention is blank on setup sheet"
Errors(4) = "-Job Number is blank on setup sheet"
For i = LBound(Errors) To UBound(Errors)
    msg = msg & Errors(i) & vbNewLine
Next i
MsgBox vbNewLine & msg, , "Missing Info"

Should work just fine for you. If not, please let me know.

answered Jul 6, 2016 at 2:01

Jim Simson

2,8803 gold badges24 silver badges31 bronze badges

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

2 Comments

alilland Over a year ago

your code works, unfortunately it doesnt work in my application of it :(

Jim Simson Over a year ago

Is it something I can help you out with?

Collectives™ on Stack Overflow

vba arrays and dynamically adding message errors to a msgbox

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