SOLVED

# VBA Function Problem

Copper Contributor

# 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

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

# Re: VBA Function Problem

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

# Re: VBA Function Problem

Thanks!

1 best response

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

# Re: VBA Function Problem

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