1

Respected Experts, I want to store each value in VBA-Array which is calculated by VBA Loop. after the looping is Done I would like to use that VBA-Array for my further Calculations.Below example explain my question more specifically.

Sub macro3()

For x = 1 To 5
xx = 1 + x
Next

End Sub

Basically the each Answer which derived i.e.(2,3,4,5,6) from above loop has to be stored in Particular Array OR in something which help me out to use each value's i.e.(2,3,4,5,6) again for my further calculations.The whole activity has to be done from VBA memory Only.Basically i don't want to use Excel Spreadsheet range to store the each Loop Value and then define that spreadsheet range as Array.

Improve this question

2 Answers 2

Reset to default
1

If you know the number of elements, then the following would create an array called 'answer' (see DIM statement) that holds the five evaluations:

Sub macro3()

Dim answer(1 To 5)
For x = 1 To 5
    answer(x) = 1 + x
Next

End Sub

I suspect though that what you're after might be a little more complicated than that, so give more detail if this is the case.

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

1 Comment

Thank you so much Both the expert's.The above given both logic's are resolved my concern.
1

In addition to CLR's answer - if you dont' know the number of elements the basic formula to ReDim the array would look something like this -

Sub macro3()
Dim x As Integer
Dim xx() As Variant

For x = 1 To 5
    ReDim Preserve xx(0 To x - 1)
    xx(x - 1) = 1 + x
Next

End Sub
Improve this answer

8 Comments

I would try to avoid the 'ReDim Preserve' part in the For-Loop. it seriously increases the calculation time.
@MarcoGetrost depends so much on what kind of situation. What you are talking is about optimization with large amounts of data, but with under 10k records it doesn't matter much. The best of course is if you can define large enough array in the very beginning and limit ReDims for example every 10k records.
i don't see the benefit in resizing an array in each iteration, if you know the length beforehand. If you have an array, where the length changes in some iterations, I agree with your approach.
@MarcoGetrost lol, this must be the me still hanging on to the memory limitations when I was a young coder (Amiga, 386 etc). The ReDim comes handy if you have smaller amount amounts of records or if you want to conserve RAM memory. The fact is nowadays computers have RAM quite enough to easily hand millions of records, that was not the the case 20 years ago. Still there are times you still want to use ReDim, my answer is yet only a very rough sample of basic functionality rather than a model for real life usage.
Further to my question is it possible to Link some other Data Array with my current array i.e.**answer** and develop one Table in VBA memory.Again I don't want to use Excel Spreadsheet. for example if I link other data Array which also contain same number of 5 elements(A,A,B,C,C) and I want to link the same with array answer and develop the Table. if such creation of table possibility is exist will I able to apply the formula like sumif on that developed Table.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.