CODEWITHSUNDEEP
arraysvbaexcel
sagar
sagar

Reputation: 397

Store VBA calculate Values in Array

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.

Upvotes: 1

Views: 2020

Answers (2)

SickDimension
SickDimension

Reputation: 912

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

Upvotes: 1

CLR
CLR

Reputation: 12279

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.

Upvotes: 1

Related Questions