I have a custom class which has a read only Value property. The Value
property can either be a variant or the Value property of an Excel
Range. The purpose of this is that the Variant is 'write once' but the
value of the Excel range may change. My property Get Value routine
tests the private Range reference and if it is Nothing it uses the
private variant variable (see code below).

So my 'constructor' routine (BTW this is VBA within Excel, not .NET)
has two optional arguments, one of type 'Variant' and the other of
type 'Range'. I can't declare the argument as type Object, of course,
because a variant isn't an object and I can't pass the Range's value
as a variant because subsequent changes to the Range.Value wouldn't be
visible inside my class.

The trouble is, my class has many such properties which can be either
an Excel range or a variant and my constructor routine has a long list
of arguments (it would be very confusing for another developer to
use!) Is there a better approach? My thought was to create another
class, say CValue, with a Range and a variant property, so I could
declare each argument in my main class as type CValue. But this
wouldn't really reduce the number of total arguments (or complexity!)
Is there any way of passing one reference that points to either a
variant or an Range.Value?

Here's some code, to show what I mean:
'------------------------------------
' This in a class module called Class1
Option Explicit

Private m_vntValue As Variant
Private m_rngRange As Excel.Range

Public Function Constructor(Optional ByVal ValueFixed As Variant, _
Optional ValueExcelRange As Excel.Range) As Boolean

If ValueExcelRange Is Nothing Then

m_vntValue = ValueFixed

Else

Set m_rngRange = ValueExcelRange

End If

End Function

Public Property Get Value() As Variant

If m_rngRange Is Nothing Then

Value = m_vntValue

Else

Value = m_rngRange.Cells(1, 1).Value

End If

End Property
'---------------------
' This in a Standard Module
Option Explicit

Private m_oTemp As Class1

Public Sub test()

Set m_oTemp = New Class1

' Either...
m_oTemp.Constructor ValueFixed:="Hello"

' ...or...
m_oTemp.Constructor ValueExcelRange:=Range("A1")

End Sub

I thought of a workaround, not very satisfying so I'm still open to
suggestions, but here it is: give the constructor method just one
variant argument and when an object is required put the object into an
array and pass the array to the constructor. The constructor would
need to check whether it receives a single value or an array
containing an object, something like this:

'------------------------------------
' This in a class module called Class1
Option Explicit

Private m_vntValue As Variant
Private m_rngRange As Excel.Range

Public Function Constructor(ByVal ValueOrRangeInAnArray As Variant) As
Boolean

On Error Resume Next

Set m_rngRange = ValueOrRangeInAnArray(0)

Err.Clear

If m_rngRange Is Nothing Then

m_vntValue = ValueOrRangeInAnArray

End If

If Err.Number < 0 Then

Err.Raise vbObjectError + 1001, "Class1", _
"Could not set the Value property."

End If

End Function

Public Property Get Value() As Variant

If m_rngRange Is Nothing Then

Value = m_vntValue

Else

Value = m_rngRange.Cells(1, 1).Value

End If

End Property
'---------------------
' This in a Standard Module
Option Explicit

Private m_oTemp As Class1

Public Sub test()

Set m_oTemp = New Class1

' Either...
m_oTemp.Constructor "Hello"

' ...or...
m_oTemp.Constructor Array(Range("A1"))

End Sub
'---------------------------------------

(onedaywhen) wrote in message . com...
I have a custom class which has a read only Value property. The Value
property can either be a variant or the Value property of an Excel
Range. The purpose of this is that the Variant is 'write once' but the
value of the Excel range may change. My property Get Value routine
tests the private Range reference and if it is Nothing it uses the
private variant variable (see code below).

So my 'constructor' routine (BTW this is VBA within Excel, not .NET)
has two optional arguments, one of type 'Variant' and the other of
type 'Range'. I can't declare the argument as type Object, of course,
because a variant isn't an object and I can't pass the Range's value
as a variant because subsequent changes to the Range.Value wouldn't be
visible inside my class.

The trouble is, my class has many such properties which can be either
an Excel range or a variant and my constructor routine has a long list
of arguments (it would be very confusing for another developer to
use!) Is there a better approach? My thought was to create another
class, say CValue, with a Range and a variant property, so I could
declare each argument in my main class as type CValue. But this
wouldn't really reduce the number of total arguments (or complexity!)
Is there any way of passing one reference that points to either a
variant or an Range.Value?

Here's some code, to show what I mean:
'------------------------------------
' This in a class module called Class1
Option Explicit

Private m_vntValue As Variant
Private m_rngRange As Excel.Range

Public Function Constructor(Optional ByVal ValueFixed As Variant, _
Optional ValueExcelRange As Excel.Range) As Boolean

If ValueExcelRange Is Nothing Then

m_vntValue = ValueFixed

Else

Set m_rngRange = ValueExcelRange

End If

End Function

Public Property Get Value() As Variant

If m_rngRange Is Nothing Then

Value = m_vntValue

Else

Value = m_rngRange.Cells(1, 1).Value

End If

End Property
'---------------------
' This in a Standard Module
Option Explicit

Private m_oTemp As Class1

Public Sub test()

Set m_oTemp = New Class1

' Either...
m_oTemp.Constructor ValueFixed:="Hello"

' ...or...
m_oTemp.Constructor ValueExcelRange:=Range("A1")

End Sub