none
Type mismatch when passing RAnge as a Function Parameter RRS feed

  • Question

  • Hi there!

    I'm writing a simple piece of code, to run through a predefined set of cells and do some calculations and return an array of values. Here's the relevant part of the code:

    Function signature

    Function LoadLeadIDArray(ByRef startcellParam As Range, ByRef endcellParam As Range, countidParam As Integer) As Variant  


    And I call the Function this way:


    Dim countid As Integer
    countid = CInt(Worksheets("Sheet2").Range("F8"))    
    leadID = LoadLeadIDArray(Worksheets("Sheet2").Range("A2"), Worksheets("Sheet2").Range("A22"), countid)

    Now, this methods works just fine. But, because my range can change, I'm trying to set variables to store a Range that is defined in the spreadsheet itself. In this way, someone operating the spreadsheet can change cell values accordingly, and the code will read the new Range values by itself. Like this:

    Dim startcell, endcell As Range        
    'Setup initial variables    
    Set startcell = Worksheets("Sheet2").Range("F9")    
    Set endcell = Worksheets("Sheet2").Range("F10")
    leadID = LoadLeadIDArray(startcell, endcell, countid) 

    But when I call the function like stated above, I've got a type mismatch, compilation error.

    Any ideas?


    Redkrine

    Saturday, October 29, 2016 11:28 AM

Answers

  • The declaration

    Dim startcell, endcell As Range

    declares startcell as a Variant, since you don't specify its data type explicitly, and not as a Range, as you probably expected. The LoadLeadIDArray function requires a Range as first argument, so a Variant is not allowed.

    So use

    Dim startcell As Range, endcell As Range

    See A Dim understanding: declaring variables in VB/VBA for more details.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Redkrine Saturday, October 29, 2016 2:31 PM
    Saturday, October 29, 2016 1:21 PM

All replies

  • The declaration

    Dim startcell, endcell As Range

    declares startcell as a Variant, since you don't specify its data type explicitly, and not as a Range, as you probably expected. The LoadLeadIDArray function requires a Range as first argument, so a Variant is not allowed.

    So use

    Dim startcell As Range, endcell As Range

    See A Dim understanding: declaring variables in VB/VBA for more details.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Redkrine Saturday, October 29, 2016 2:31 PM
    Saturday, October 29, 2016 1:21 PM
  • Hi Hans,

    Thanks for your prompt reply! It works!



    Redkrine

    Saturday, October 29, 2016 2:53 PM