Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: UBound: Type mismatch in VBA function

    Hi,

    I am a newbie when it comes to writing VBA macros in Excel, so I will most likely get the jargon wrong. I have hit a dead end in searching for an explanation to an odd problem that I am having determining the size of array passed from Excel 2007 into my VBA function (macro). I will demonstrate with two examples.

    Case 1 -------------------
    An Excel cell has the following formula: =myFunction(A1:A6)

    The macro is as follows:

    Function myFunction(myArray As Variant)

    Debug.Print IsArray(myArray)
    myFunction = UBound(myArray, 1)

    End Function

    The Debug.Print statement outputs True, confirmning that myFunction is receiving an array. Yet the Excel cell evaluates to #VALUE! because of a type mismatch at the UBound assignment.

    From what I have read the above should work. Contrast this with the following variation.

    Case 2 -------------------
    An Excel cell has the following formula: =myFunction1(A1:A6)

    The macro is as follows:

    Function myFunction1(myArray As Variant)

    Temp = myArray

    Debug.Print IsArray(Temp)
    myFunction1 = UBound(Temp, 1)

    End Function

    This works.
    -------------------

    I don't understand what Temp = myArray does. Does it transfer a pointer? More importantly, why does it make the funciton work?

    This is driving me nuts! Please. Help.

    Cheers,

    Sebulba1

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by sebulba1
    Hi,

    I am a newbie when it comes to writing VBA macros in Excel, so I will most likely get the jargon wrong. I have hit a dead end in searching for an explanation to an odd problem that I am having determining the size of array passed from Excel 2007 into my VBA function (macro). I will demonstrate with two examples.

    Case 1 -------------------
    An Excel cell has the following formula: =myFunction(A1:A6)

    The macro is as follows:

    Function myFunction(myArray As Variant)

    Debug.Print IsArray(myArray)
    myFunction = UBound(myArray, 1)

    End Function

    The Debug.Print statement outputs True, confirmning that myFunction is receiving an array. Yet the Excel cell evaluates to #VALUE! because of a type mismatch at the UBound assignment.

    From what I have read the above should work. Contrast this with the following variation.

    Case 2 -------------------
    An Excel cell has the following formula: =myFunction1(A1:A6)

    The macro is as follows:

    Function myFunction1(myArray As Variant)

    Temp = myArray

    Debug.Print IsArray(Temp)
    myFunction1 = UBound(Temp, 1)

    End Function

    This works.
    -------------------

    I don't understand what Temp = myArray does. Does it transfer a pointer? More importantly, why does it make the funciton work?

    This is driving me nuts! Please. Help.

    Cheers,

    Sebulba1
    Hi

    It is not clear what you are trying to achieve (unless you really do want to know how many Rows are selected, but I would use the Range object (for that is what it is) ie.
    Code:
    Option Explicit
    
    Function myFunction(myArray As Range) As Long
    
        myFunction = myArray.Rows.Count
    
    End Function
    I would also recommend using Option Explicit to force variable declarations and syntax check. This can be done automatically by setting Require variable Declaration on the Edit tab on the Tool -> Option menu.

    HTH


    MTB

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    Hi MTB,

    Thanks for reply. I had left out the meat of the code since I didn't want it to distract from the issue.

    The actual function will receive a second parameter, in integer N. The goal is to take in an array of grades, sum the top N best and return the result.

    I need to be able to determine whether a 1D or a 2D array is passed (for error checking). And, in the case of a 1D array I think that I need to know whether it is a row or a column vector. I will look into getting information on how ot use range objects.

    Thanks also for the suggestion on the Option Explicit. I believe in tight programming. But until I could resolve my array passing ussue I had no idea of how to declare varaibles.

    Cheers,

    Sebulba1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •