Trying to store user values into an array

user_19498101 0
Viewed 39

I am trying to create an array of a user set length that users can add values to that will be saved and output on multiple sheets. I am very new to VBA so I do not know if what I am trying to do is possible but I have tried a few methods unsuccessfully.

This is the code I have so far

Sub addCost()
    Dim costArray() As Variant
    ReDim Preserve costArray(1 To Range("b2").Value)

    For i = 1 To Range("b2").Value
        costArray(i) = 0
    Next i

    Dim newCost As Variant
    Dim costYear As Variant

    newCost = InputBox("New cost amount:")
    costYear = InputBox("Year new cost is active:")

    costArray(costYear) = newCost
End Sub

Here is what the input tab looks like in excel

With the length of the array being the project lifespan and the add new cost activating the code, clear costs are still in progress. Is there a way for the array to store after multiple executions of the addCost sub?

Thanks!

vba excel
1 Answers

This link Microsoft shows some methods of store values after the macro ending (in your case, sub addCost)

I think the first solution will be good for you.

Other solutions is use Access to store data (specially if you need these data over time) or a new and clean worksheet where you can store array entries in a cell (This is a very practical solution if the number of entries does not get too big)

user_176549880
Related