VBA Dynamic array : change size and use of Get Let Property

Question

The problem: I would like to create a dynamic array that change size when an event occurs (Event Calculate), by preserving its content and extending by one its size. Let's say I want to do this for a vector of double (and also a date type). The data is updating in a specific cell.

My understanding: I am coding in the Event "Calculate of Excel". I cannot use "Public", I have to declare the array as private and then use Get and Let Property... But, how can I use Redim Preserve in this case ? Also, I think I am probably missing some points on how it has to be used: Here s a sample of the code I wrote:

Name of the Classe: "Class1" Code of the class:

Code:

Option Explicit
Private IntradayValueSerie1() As Double 'Dynamic vector which will contain Y1 Values
Public Counter As Long
Public Property Let vIntradayValueSerie1(ByVal Counter_Value As Long)
      IntradayValueSerie1(Counter_Value) = Sheets("Sheet1").Range("C5")
End Property
Public Property Get vIntradayValueSerie1(ByVal Counter_Value As Long) As Variant
      vIntradayValueSerie1(Counterr) = IntradayValueSerie1
End Property

So I want "Let" To attribute the new value of my extended array I want "Get" to return the array (extended and updated) Remark: Counter will be updated and increase at then end of the in the section Event "Worksheet.Calculate"

Test code (HAS TO BE in the section Event "Worksheet.Calculate")

Code:

counter = 1

Dim Serie1 As New Class1
Serie1.IntradayValueSerie1(Counter) = ??? I don't know how to use the property to initialize the vector

counter = counter +1
 ReDim Preserve IntradayValueSerie1(Counter)

Also, As I want to return an array, do I have to set Variant for the Get property ? As you can see, some points make me confused, either on the use and the structure.

Thank you for you time !

Community · Accepted Answer · 2020-06-20 09:12:55Z

edited to match the following assumptions

a Sub in any of your modules initializes and uses a variable of Class1 type

I'll call it after Sub ExploitClass1(), but you can rename it as you like
that Sub writes into any cell of the relavant worksheet whose calculate event you want to use to update the dynamic array property of your variable of Class1 type

I'll assume that relevant worksheet is named after "CalculateClass": you can rename it as you like but be sure to fill its code pane with what you'll find in "your relevant worksheet code pane" section of this answer

then proceed like follows:

your Class1 code pane

Option Explicit

Private IntradayValueSerie1() As Double 'Dynamic array which will contain Y1 Values
Private counter As Long '<-- counter to track the size of the dynamic array

Public Sub WriteValue(ByVal Value As Variant) '<-- class method to write a value in the last dynamic array slot
    IntradayValueSerie1(counter) = Value
    Extend
End Sub

Private Sub Extend() '<-- class method to extend dynamic array size by one
    counter = counter + 1 '<-- update the dynamic array size counter by one
    ReDim Preserve IntradayValueSerie1(1 To counter) '<-- increase the dynamic array size
End Sub

Private Sub Class_Initialize()
    counter = 1
    ReDim IntradayValueSerie1(1 To counter) '<-- at class instantiating, initialize the dynamic array
End Sub

'-----------------------------------------------
' added methods to "query" some dynamic array related values
'-----------------------------------------------
Public Function GetCounter() As Long '<-- class method to retrive the current counter (i.e. the dynamic array size) value
    GetCounter = counter '<-- return counter
End Function

Public Function GetPenultimateArrayValue() As Variant '<-- class method to retrive the current counter (i.e. the dynamic array size) value
    GetPenultimateArrayValue = IntradayValueSerie1(counter - 1) '<-- return dynamic array one before second to last element
End Function

your Sub

Option Explicit

Public Serie1 As Class1 '<-- declare a Public variable of type Class1

Sub ExploitClass1()
    Set Serie1 = New Class1 '<-- instantiate a new public object of type Class1
    
    Worksheets("CalculateClass").Range("A1") = 1 ' make something that triggers calulate event in the relevant worksheet: in this case I had cell "A2" of that worksheet with a formula `= A1+1`
    
    MsgBox Serie1.GetPenultimateArrayValue & " - " & Serie1.GetCounter 'show your Class1 dynamic array has been updated exploiting those "querying" methods we added at the bottom of your class
End Sub

your relevant worksheet code pane

it'll use the Public object of Class1 type we declared and initialized in ExploitClass1() sub

Option Explicit

Private Sub Worksheet_Calculate()
    Serie1.WriteValue Worksheets("Sheet01").Range("C5").Value '<-- this will write the passed value to your class dynamic array last slot
End Sub

@LaGabriella, it'd be nice of you giving proper feedbacks at people trying at helping you. thank you

Collectives™ on Stack Overflow

VBA Dynamic array : change size and use of Get Let Property

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related