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_HoldingSheetN ame As String
Private m_rngFundSymbol s As Range
*
*
Option Explicit
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Private Sub Class_Initializ e()
m_HoldingSheetN ame = ""
*
*
End Sub
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Private Sub Class_Terminate () <==being invoked in error
Set m_rngFundSymbol s = Nothing
End Sub
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Let SheetName(Sheet ID As String)
If Not SheetID = "" Then
m_HoldingSheetN ame = SheetID
End If
End Property
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Get SheetName() As String
SheetName = m_HoldingSheetN ame
End Property
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Let SymbolRange(Sym bRange As Range)
Set m_rngFundSymbol s = SymbRange
End Property
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Get SymbolRange() As Range
Set SymbolRange = m_rngFundSymbol s
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).Sh eetName = "PE Holdings"
AcctSheet(1).Sy mbolRange = Range("PECatego ries") <==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_HoldingSheetN ame As String
Private m_rngFundSymbol s As Range
*
*
Option Explicit
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Private Sub Class_Initializ e()
m_HoldingSheetN ame = ""
*
*
End Sub
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Private Sub Class_Terminate () <==being invoked in error
Set m_rngFundSymbol s = Nothing
End Sub
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Let SheetName(Sheet ID As String)
If Not SheetID = "" Then
m_HoldingSheetN ame = SheetID
End If
End Property
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Get SheetName() As String
SheetName = m_HoldingSheetN ame
End Property
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Let SymbolRange(Sym bRange As Range)
Set m_rngFundSymbol s = SymbRange
End Property
~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~ ~~~~~
Public Property Get SymbolRange() As Range
Set SymbolRange = m_rngFundSymbol s
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).Sh eetName = "PE Holdings"
AcctSheet(1).Sy mbolRange = Range("PECatego ries") <==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...
Comment