I am trying to make Excel VBA class member function fReadData
which returns some values from the Excel spreadsheet and uses the class definition ClassOne
, only for testing purposes.
' ClassOne
Option Explicit
Private pOne As Double
Private Sub Class_Initialize()
End Sub
Private Sub Class_Terminate()
End Sub
Public Property Get One() As Double
One = pOne
End Property
Public Property Let One(lOne As Double)
pOne = lOne
End Property
Property Get fReadData(alue As Range) As Double
Dim oFreadData As New ClassOne
oFreadData.One = alue.Columns(1)
fReadData = oFreadData.One
End Property
The test-use of the class is as follows
' main module
Option Explicit
Dim oOne As New ClassOne
Function Test(alue As Range) As Double
oOne.fReadData (alue)
Test = oOne.One
End Function
The function Test
should read cell (B1) from the spreadsheet and put the answer to cell (B3). Now the results is "A value used in the formula is of the wrong data type".
If I omit the member function fReadData
the code works
' main module
Option Explicit
Dim oOne As New ClassOne
Function Test(alue As Range) As Double
'oOne.fReadData (alue)
oOne.One = alue.Columns(1)
Test = oOne.One
End Function
The result is as expected:
In this statement
oFreadData.One = alue.Columns(1)
the array of values assigns to the variable of the type of Double which is wrong.
alue.Columns(1)
returns all cells of the first column of `alue'.
You can express this as that:
oFreadData.One = alue.Cells(1)
The values of the first cell of alue
assigns to One
.
What's else can (should) be corrected/improved in your code?
Property Get fReadData(alue As Range) As Double
such usage is illegal (not possible to provide parameters to the Property Get subroutine), use the function instead.fReadData
which is not necessary. Just operate by existing object:Public Function fReadData(alue As Range) As Double
pOne = alue.Cells(1)
fReadData = pOne
End Function