CODEWITHSUNDEEP
excelvba
AhlawSahla
AhlawSahla

Reputation: 17

Public Property passing to other sheet modules

I have a VBA script that uses a Private Sub Open_Workbook() to initialize some arrays from one of the excel sheets to be used as lookup tables later. The Private Sub Open_Worbook() is placed in the ThisWorkbook module and it appears to do it's job when the excel workbook is open. The arrays in the Open_Workbook() sub are Dim as Variants but I have not been able to "pass" them along to Sheet1 for example to be used in the Sub for that sheet. I have tried using an accessor similar to what was suggested at the following link: Create and assign variables on Workbook_open, pass it to Worksheet_change Here is my code following that suggestion. Following code is in the ThisWorkbook module of the excel workbook:

Option Explicit
Private Test_Array() As Variant

Private Sub Workbook_Open()
' Code here to redim preserve Test_array and set elements of the array and other code that 
' Added snippits of code to 
Dim Test_Array() As Variant
For i = 1 To UBound(TestRange,2)
     ReDim Preserve Test_Array(i - 1)
     Test_Array(i-1) = TestRange(1,i) 
Next i
End Sub 

Public Property Get TestArray() As Variant
    TestArray = Test_Array()
End Property

I was hoping that I would be able to use Thisworkbook.TestArray in Sheet1 to do some calculations on it but when I have a Msgbox ThisWorkbook.TestArray(0), I am getting a Subscript out of range error. I debugged the code and it appears that the Public Property Get TestArray(), Test_Array() is empty. What am I doing wrong? Can I not use Variant with Public Get Property? I did confirm that the Test_Array in the Workbook_Open() Sub is indeed populated with the expected elements.

Edited: Added code for populating Test_Array

Upvotes: 0

Views: 102

Answers (1)

Storax
Storax

Reputation: 12177

You have a Dim Test_Array() As Variant on module level and in the Private Sub Workbook_Open(). So you never populate Test_Array on the module level.

The following code is working for me

Workbook class module:

Option Explicit
Private Test_Array() As Variant

Private Sub Workbook_Open()
' Code here to redim preserve Test_array and set elements of the array and other code that
' Added snippits of code to
'Dim Test_Array() As Variant
Dim testrange
Dim i As Long
testrange = Sheet1.Range("A1:A4").Value
For i = 1 To UBound(testrange, 2)
     ReDim Preserve Test_Array(i - 1)
     Test_Array(i - 1) = testrange(1, i)
Next i
End Sub

Public Property Get TestArray() As Variant
    TestArray = Test_Array()
End Property

Normal module

 Option Explicit

Sub testit()
    Debug.Print ThisWorkbook.TestArray(0)
End Sub

Reading on Lifetime of variables

Upvotes: 0

Related Questions