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

cashonlyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi

pls try

Function testit2() As Variant()

    Dim testarr() As Variant
    Dim testarr1 As Variant

    testarr = Range("A4:A31") ' works
    testarr1 = ThisWorkbook.Worksheets("FormulaTemplate").Range("A4:A31") 
    
    testit1 = testarr
    testit2 = testarr1


End Function 

Open in new window

see Locals type for difference

Regards
0
 
[ fanpages ]IT Services ConsultantCommented:
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.
0
 
[ fanpages ]IT Services ConsultantCommented:
:)

^ What Rgonzo1971 said!
0
 
cashonlyAuthor Commented:
Rgonzo1971, thanks, spot on

fanspage, sorry, Rgonzo1971 beat you to the punch.
0
 
[ fanpages ]IT Services ConsultantCommented:
Yeah... I attempted to answer your question, that slowed me down! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.