Forum Discussion
VBA Function Problem
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
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:
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 =.
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:
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!
