SOLVED

VBA Function Problem

Copper Contributor

I was trying to confirm that the statements below are equivalent.

=rangename.Cells(1,1)

=rangename.Value

The VBA functions below (ValA, ValB) were used to confirm that the statements are equivalent whether Var is a single cell range or a muti-cell range. When I moved the comparison of the statements inside the VBA function below (CellValueTest), the combined function works for a single cell range but returns the error #VALUE! when I use a multi-cell range. Any suggestions?

 

Public Function ValA(Var As Range)

    ValA = Var.Cells(1, 1)

End Function

 

Public Function ValB(Var As Range)

    ValB = Var.Value

End Function

 

Public Function CellValueTest(Var As Range) As Boolean

    VA = Var.Cells(1, 1)

    VB = Var.Value

    If VA = VB Then

        CellValueTest = True

    Else

        CellValueTest = False

    End IF

End Function

2 Replies
best response confirmed by MEG1954 (Copper Contributor)
Solution

ValA returns a single value - the value of the top left cell in the range Var.

ValB returns a two-dimensional  array containing the values of all cells in the range Var.

 

I filled the range A1:B3 with numbers

In the Visual Basic Editor:

 

Debug.Print TypeName(ValA(Range("A1:B3")))

Double

 

Debug.Print TypeName(ValB(Range("A1:B3")))

Variant()

 

The () indicate that it is a variant array.

 

If I use the functions in cell formulas:

 

HansVogelaar_0-1704371821962.png

 

As you see, the functions are not equivalent.

CellValueTest returns an error because ValA and ValB return different data types that cannot be compared using =.

Thanks!

1 best response

Accepted Solutions
best response confirmed by MEG1954 (Copper Contributor)
Solution

ValA returns a single value - the value of the top left cell in the range Var.

ValB returns a two-dimensional  array containing the values of all cells in the range Var.

 

I filled the range A1:B3 with numbers

In the Visual Basic Editor:

 

Debug.Print TypeName(ValA(Range("A1:B3")))

Double

 

Debug.Print TypeName(ValB(Range("A1:B3")))

Variant()

 

The () indicate that it is a variant array.

 

If I use the functions in cell formulas:

 

HansVogelaar_0-1704371821962.png

 

As you see, the functions are not equivalent.

CellValueTest returns an error because ValA and ValB return different data types that cannot be compared using =.

View solution in original post