Forum Discussion

MEG1954's avatar
MEG1954
Copper Contributor
Jan 03, 2024
Solved

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 equivalen...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jan 04, 2024

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

HansVogelaar's avatar
HansVogelaar
MVP
Jan 04, 2024

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