asked on

Problem converting an Excel Range to a VBA array

In this function, why does the first array assignment line work and the second give a type mismatch? Don't they point to the same thing?

Function testit2() As Variant()

    Dim testarr() As Variant
    
    testarr = Range("A4:A31") ' works
    testarr = ThisWorkbook.Worksheets("FormulaTemplate").Range("A4:A31") ' Gives Type Mismatch error
    
    testit2 = testarr

End Function

Open in new window

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

[ fanpages ]

Hi,

It is the use of the () parenthesis that implies that the testarr variable is defined as an Array.

Try this instead:

Function testit2() As Variant

    Dim testarr As Variant
    
    testarr = Range("A4:A31") ' works
    testarr = ThisWorkbook.Worksheets("FormulaTemplate").Range("A4:A31") ' Now works!
    
    testit2 = testarr

End Function

Open in new window

You could test this as follows:

Public Sub Test()

  ThisWorkbook.Worksheets("FormulaTemplate").Range("B4:B31") = testit2()
  
End Sub

Open in new window

The contents of the range [A4:A31] should be copied to the range [B4:B31].

BFN,

fp.

[ fanpages ]

:)

^ What Rgonzo1971 said!

cashonly

ASKER

Rgonzo1971, thanks, spot on

fanspage, sorry, Rgonzo1971 beat you to the punch.

[ fanpages ]

Yeah... I attempted to answer your question, that slowed me down! :)