excelvbaclassmember-functions

VBA class member function and class definition problem


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".

enter image description here

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:

enter image description here


Solution

  • 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?

    1. 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.
    2. The new object is created in 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
    

    enter image description here