Variable number of variables in VBA

[Yamezz]

As part of a macro, I'd like to run a form of error checking on sales orders. Basically, if the quantity of an order is not a whole number, then there's been an error in upstream processing. I'd like to keep a list of these problem orders, to display in a message box at the end of the macro. Something like "There were errors with orders #1839, #1923, #1934"

How do I handle the variable declarations and use as the errors are found?
I'm thinking something like the first error encountered is allocated to varError1, the second to varError2 and so on. I'd need some kind of incremental count of the number of errors.
The end result in the above example would see varError1=1839, varError2=1923 and varError3=1934

 

[HighAndWilder]

As part of a macro, I'd like to run a form of error checking on sales orders. Basically, if the quantity of an order is not a whole number, then there's been an error in upstream processing. I'd like to keep a list of these problem orders, to display in a message box at the end of the macro. Something like "There were errors with orders #1839, #1923, #1934"

How do I handle the variable declarations and use as the errors are found?
I'm thinking something like the first error encountered is allocated to varError1, the second to varError2 and so on. I'd need some kind of incremental count of the number of errors.
The end result in the above example would see varError1=1839, varError2=1923 and varError3=1934

What do you want to do with this list of errors?

 

[Yamezz]

What do you want to do with this list of errors?

A simple messagebox at the end of the macro "There were errors with orders #1839, #1923, #1934"

 

[HighAndWilder]

A simple messagebox at the end of the macro "There were errors with orders #1839, #1923, #1934"

So are you going to write them down and take some action?

 

[Yamezz]

So are you going to write them down and take some action?

Yes. I then have to manually go into the source file and work out why the problem has occurred.

 

[HighAndWilder]

Yes. I then have to manually go into the source file and work out why the problem has occurred.

You could apply a filter to the data without having to use any code.

 

[FormR]

Hi, you could either store the error order numbers in an array variable, which you could then Join() together to display in a Msgbox. Or if you really only want to display them in a message box you could just keep concatenating the error order numbers to an existing string variable as you come across them with an appropriate delimiter.

 

[Yamezz]

Hi, you could either store the error order numbers in an array variable, which you could then Join() together to display in a Msgbox. Or if you really only want to display them in a message box you could just keep concatenating the error order numbers to an existing string variable as you come across them with an appropriate delimiter.

Thanks. I think the latter approach will work.

The error is only an error after some upstream calculation in the macro (prior to the calculation it's impossible to tell it's an error), hence I can't filter because I don't know yet if there's any errors.
Unfortunately, I need to just write the orders down and go back into the source data file to manually work out what has happened to cause the error.