none
Run time error: type mismatch. Where?! RRS feed

  • Question

  • I have this program and I cannot spot the type mismatch anywhere. Essentially, my sub program passes arrays and double values into my function BlackBox. The function returns a 2D array with some cells having text, other cells having numbers. I created a variable ResultsRowCount to equal a specific cell in this 2D array, which is a number. But every time I run the program I get a type mismatch (See text below, I point out where the error is). Here is some of the code (the program is too long and confusing to put it all up here).

    Sub Calculate() Dim rowcount As Double Dim ItemArray() As String Dim DescriptionArray() As String Dim SoundPressureArray() As Double Dim Frequency As Double Dim NCReceiver As Double '***Yada yada yada. Skipping over this code until...*** 'Declare Arrays to hold values after the sorting has completed Dim SortedItemArray() As String Dim SortedDescriptionArray() As String Dim SortedSoundPressureArray() As Double 'Size Arrays to hold values after the sorting has completed ReDim SortedItemArray(rowcount) ReDim SortedDescriptionArray(rowcount) ReDim SortedSoundPressureArray(rowcount) 'Fill Arrays to hold values after the sorting has completed For i = 0 To rowcount - 1 SortedItemArray(i) = Worksheets("TemporaryCalc").Cells(i + firstrow, 2).Value SortedDescriptionArray(i) = Worksheets("TemporaryCalc").Cells(i + firstrow, 3).Value SortedSoundPressureArray(i) = Worksheets("TemporaryCalc").Cells(i + firstrow, 4).Value Next i 'Send sorted arrays in the BlackBox for calculation Dim Results As Variant Results = BlackBox(SortedItemArray, SortedDescriptionArray, SortedSoundPressureArray, rowcount, Frequency, NCReceiver, firstrow, headerrow) '"Export" results from BlackBox into this module for manipulation Dim ResultsRowCount As Double ResultsRowCount = Results(0, 3) '***THIS IS WHERE MY ERROR OCCURS. TYPE MISMATCH*** For i = 0 To ResultsRowCount Worksheets("TemporaryCalc").Cells(5 + i + ResultsLastRow, 12).Value = Results(i, 0) Worksheets("TemporaryCalc").Cells(5 + i + ResultsLastRow, 13).Value = Results(i, 1) Worksheets("TemporaryCalc").Cells(5 + i + ResultsLastRow, T + 14).Value = Results(i, 2) Next i ResultsLastRow = ResultsLastRow + ResultsRowCount Next T End Sub ***Here is my function BlackBox*** Function BlackBox(SortedItemArray() As String, SortedDescriptionArray() As String, SortedSoundPressureArray() As Double, rowcount As Double, Frequency As Double, NCReceiver As Double, firstrow As Integer, headerrow As Integer) As Variant '***Function in here***

    End Function


    Thursday, August 13, 2015 5:59 PM

All replies

  • Before the problem row insert this code and let us know what is printed in the Immediate Window

    'Send sorted arrays in the BlackBox for calculation
        Dim Results As Variant
        Debug.Print "1st Dimension: "& UBound(Results, 1)
        Debug.Print "2nd Dimension: "& UBound(Results, 2)
    Results = BlackBox(SortedItemArray, SortedDescriptionArray, SortedSoundPressureArray, rowcount, Frequency, NCReceiver, firstrow, headerrow)

    If the 2nd Dimension line returns an error then you only have a single dimension array so replace Results(0,3) with Results(3). If you get 2 numbers in the Immediate Window the second one must be 3 or more


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Thursday, August 13, 2015 10:15 PM
  • I copied your code into my program in a row above where I mentioned my problem was. I had to delete your line of code that declares Dim Results As Variant as I have already declared it a few lines above.

    I am getting still getting a Run-time Error '13': Type Mismatch on your line of code: Debug.Print "1st Dimension: " & UBound(Results, 1)

    Any reason why?

    Thanks

    Friday, August 14, 2015 2:22 PM

  • Sorry, put the Debug.Print lines after the
     Results = BlackBox(SortedItem
    row as that is the line that builds the array. The Ubound statement is about seeing how big teh resulting array is. You may be trying to access an element that does not exist, hence your error.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Monday, August 17, 2015 9:53 AM
  • Re:  You may be trying to access an element that does not exist

    Rod,
    That's what he is doing...
    See the OP's duplicate post on Aug. 14, 2015 titled... "Type Mismatch...Somebody Save Me!"
    That post includes all of the code.
    '---

    Jim Cone
    http://www.5z8.info/banned-in-the-US_k4g0qk_refinance-now

    • Edited by James Cone Monday, August 17, 2015 11:29 AM
    Monday, August 17, 2015 11:26 AM