Is it possible to have a multidimensional dynamic array?
I'm new to VBA and can't get to grips with arrays as well as I would like.
I need to capture data from forms and at present I'm having to have seperate arrays for each item I want to capture.
I really want to be able to have the first element representing the event and then the child elements below holding the necessary info.
At present I'm doing something like this:
arrProd(x) = strProd
arrQty(x) = strQty
arrProdCde(x) = strProdCde
I would much rather have:
arrProd(x,1) = strProd
arrProd(x,2) = strQty
I can only find UBound as a function to give max length but whenever I try ReDim Preserve arrProd(maxLen +1, 3) I get an error.
I've tried variations on this and no joy. This is causing me to grey early!
Its upsetting me that I can do similar things to this in other languages but not with VBA.
Surely it is possible to do this? (or is it? and if not why the hell not!)
If some one can help I would more than grateful.
Thanks
Gareth
I'm new to VBA and can't get to grips with arrays as well as I would like.
I need to capture data from forms and at present I'm having to have seperate arrays for each item I want to capture.
I really want to be able to have the first element representing the event and then the child elements below holding the necessary info.
At present I'm doing something like this:
arrProd(x) = strProd
arrQty(x) = strQty
arrProdCde(x) = strProdCde
I would much rather have:
arrProd(x,1) = strProd
arrProd(x,2) = strQty
I can only find UBound as a function to give max length but whenever I try ReDim Preserve arrProd(maxLen +1, 3) I get an error.
I've tried variations on this and no joy. This is causing me to grey early!
Its upsetting me that I can do similar things to this in other languages but not with VBA.
Surely it is possible to do this? (or is it? and if not why the hell not!)
If some one can help I would more than grateful.
Thanks
Gareth