E
Enigman O'Maly
I'm trying to convert some VBA routines in an Excel 2000 workbook to use
objects instead of global varaibles. I've defined a class module with
the following (excerpts):
(from SheetMetrics class module)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private m_HoldingSheetName As String
Private m_rngFundSymbols As Range
*
*
Option Explicit
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Class_Initialize()
m_HoldingSheetName = ""
*
*
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Class_Terminate() <==being invoked in error
Set m_rngFundSymbols = Nothing
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Let SheetName(SheetID As String)
If Not SheetID = "" Then
m_HoldingSheetName = SheetID
End If
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Get SheetName() As String
SheetName = m_HoldingSheetName
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Let SymbolRange(SymbRange As Range)
Set m_rngFundSymbols = SymbRange
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Get SymbolRange() As Range
Set SymbolRange = m_rngFundSymbols
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In a worksheet code module I attempt the following (PECategories is a
named range on the worksheet):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim AcctSheet(4) As SheetMetrics <== class module above
Set AcctSheet(1) = New SheetMetrics
Set AcctSheet(2) = New SheetMetrics
Set AcctSheet(3) = New SheetMetrics
Set AcctSheet(4) = New SheetMetrics
AcctSheet(1).SheetName = "PE Holdings"
AcctSheet(1).SymbolRange = Range("PECategories") <==problem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When the last statement above SHOULD execute, the
Class_Terminate method kicks off instead.
I'm stumped - would be grateful for any suggestions what is causing this
to happen...
objects instead of global varaibles. I've defined a class module with
the following (excerpts):
(from SheetMetrics class module)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private m_HoldingSheetName As String
Private m_rngFundSymbols As Range
*
*
Option Explicit
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Class_Initialize()
m_HoldingSheetName = ""
*
*
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Class_Terminate() <==being invoked in error
Set m_rngFundSymbols = Nothing
End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Let SheetName(SheetID As String)
If Not SheetID = "" Then
m_HoldingSheetName = SheetID
End If
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Get SheetName() As String
SheetName = m_HoldingSheetName
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Let SymbolRange(SymbRange As Range)
Set m_rngFundSymbols = SymbRange
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Public Property Get SymbolRange() As Range
Set SymbolRange = m_rngFundSymbols
End Property
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In a worksheet code module I attempt the following (PECategories is a
named range on the worksheet):
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Dim AcctSheet(4) As SheetMetrics <== class module above
Set AcctSheet(1) = New SheetMetrics
Set AcctSheet(2) = New SheetMetrics
Set AcctSheet(3) = New SheetMetrics
Set AcctSheet(4) = New SheetMetrics
AcctSheet(1).SheetName = "PE Holdings"
AcctSheet(1).SymbolRange = Range("PECategories") <==problem
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When the last statement above SHOULD execute, the
Class_Terminate method kicks off instead.
I'm stumped - would be grateful for any suggestions what is causing this
to happen...