1

The issue i'm experiencing is that there is a "Type Mismatch" error after which the Locals Window loses all memory of the values stored in the variables. Therefore, I cannot debug where the problem is coming from.

Preliminary code prior to the occurrence of the issue:

Dim i as Long, j as Long, g as Long
Dim Total_Rows_Help as Long

Total_Rows_Help = Worksheets("Help Worksheet").Range("A" & Rows.Count).End(xlUp).Row

ReDim Min_NDate(2 To Total_Rows_Help, 2 To Total_Rows_Help) As Variant

For i = LBound(Min_NDate, 1) To UBound(Min_NDate, 1)
    For j = LBound(Min_NDate, 2) To UBound(Min_NDate, 2)
        Min_NDate(i, j) = Worksheets("Help Worksheet").Cells(i, 2) - Worksheets("Help Worksheet").Cells(j, 2)
    Next j
Next i

The problem occurs around these sets of loops (the loop and code works until a certain point where it errors. I believe it is due to the Application.Index):

ReDim Count(2 To Total_Rows_Help, 2 To Periods - 1) As Variant
For i = LBound(Min_NDate, 1) To UBound(Min_NDate, 1)
    If Application.Large(Application.Index(Min_NDate, i, 0), Periods - 1) < 0 Then
        For g = LBound(Count, 2) To UBound(Count, 2)
            Count(i, g) = Application.Large(Application.Index(Min_NDate, i, 0), g)
        Next g
    End If
Next i

Below is the attached excel file and the code is located at the Compiled Worksheet: Excel File

Weirdly there are times where it does not allow a Debug option and just instantly ends. However, this time after tweaking the i values, I was able to get a screenshot of the issue: 1

2

It crashed on i values that is +1 away from reaching the upper bound of the loop. However, it should work because Min_NDate and Count's number of columns are the same with 2 To Total_Rows_Help

Same problem with the upper bound of the variable, i, was set using the UBound function:

3

4

Community
  • 1
  • 1
Pherdindy
  • 1,168
  • 7
  • 23
  • 52
  • Depending on the size of the slice index will throw a fit and you will have an empty array. Test values of i and see. Somewhere round 65536? Maybe. Can't remember. https://stackoverflow.com/questions/48598247/slice-array-to-use-index-on-larger-than-65000 – QHarr Feb 25 '18 at 16:39
  • If you hit "debug" in that error dialog box, the code should stop with the relevant line highlighted, and the values of the variables retained. – Ron Rosenfeld Feb 25 '18 at 20:12
  • @QHarr thanks testing the i values at the moment. Will update the main post – Pherdindy Feb 26 '18 at 01:18
  • @RonRosenfeld It just ends abruptly I do not have an option to press debug most of the time. It is just "Ok" I believe – Pherdindy Feb 26 '18 at 01:19
  • 1
    @MarcSantos - there are a number of other debugging options. [This post](http://www.cpearson.com/excel/DebuggingVBA.aspx) *(by VBA magician [Chip Pearson](http://www.cpearson.com/Excel/Topic.aspx))* outlines some other things to try to get to the bottom of it, the first being **stepping through your code**. – ashleedawg Feb 26 '18 at 04:22
  • Yes I do step through my code, however, in this case, the debugging just ends abruptly. There are some times that it works perhaps it has something to do with the main issue as mentioned by Ron. – Pherdindy Feb 27 '18 at 04:07

1 Answers1

2

The error is because your INDEX function is not pointing where you think it is.

Index(Min_NDate, i, 0)

will return the entire row of the ith row of the index. However, the LBound of your Min_NDate array is 2. And that is the first row of the array.

So Index(Min_Ndate,1,0) actually returns the contents of Min_Ndate(2,{2-Ubound(Min_NDate,2}) and, it follows, that Index(Min_Ndate, Ubound(Min_NDate,1), 0) will be outside your actual array.

You need to adjust the values in the Index function.

There are likely other issues in your code. After adjusting that entry, I found another instance where your references to Min_NDate are out of the bounds for the way you have dimensioned that array.

Running the following code gives a demonstration of what I am trying to write. And after i=9, the next "run" will result in a Type Mismatch error for the same reason as your code.

Option Explicit
Sub marine()
    Dim v(2 To 10, 2 To 5)
    Dim i As Long, j As Long
    Dim w
    Dim s As String

'Populate the array
For i = 2 To 10
    For j = 2 To 5
        v(i, j) = i * j
    Next j
Next i

'Examine the contents
For i = 2 To 10
    'contents of the array
    s = ""
    For j = 2 To 5
        s = s & vbLf & v(i, j)
    Next j
    s = "Array contents with i = " & i & s & vbLf & vbLf

    'Output of the index function with the same arguments
    w = "Output of Index Function with i = " & i & vbLf & Join(Application.Index(v, i, 0), vbLf)
    MsgBox s & w
Next i


End Sub
Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Right I removed the comment a few seconds after posting to actually try to understand it. Sorry about that. It does make a little sense now why it does not work – Pherdindy Feb 26 '18 at 02:19
  • @MarcSantos OK I have deleted my comment. Is what I wrote understandable now? – Ron Rosenfeld Feb 26 '18 at 02:24
  • Yes I have an idea still trying to properly visualize it so I can revise my code. – Pherdindy Feb 26 '18 at 02:28
  • @MarcSantos I just added a macro which, I think, may help in visualizing the problem. – Ron Rosenfeld Feb 26 '18 at 02:30
  • Thanks will try it out will update once I fix the code – Pherdindy Feb 26 '18 at 02:38
  • I do not understand what you mean by `Min_NDate(2,{2-Ubound(Min_NDate,2)}`. What does the ones in the bracket mean? What I got was `Min_NDate(2,2-199)` = `Min_NDate(2,-197)`. Also to clarify, `Index(Min_NDate,1,0)` will return all the columns of the first row of the array `Min_NDate` so it will return the column values of `Min_NDate(2,:)` am I correct? – Pherdindy Feb 27 '18 at 07:12
  • @MarcSantos `{2-Ubound(Min_NDate,2}` represents the range from `2` to `Ubound(Min_NDate,2)`. It is **NOT** a legitimate VBA line. So it was a shorthand method of representing all of the entries in the `ith` row of the array, where `row` is equivalent to the first dimension of the array. i.e. the same as your shorter `Min_NDate(2,:)` – Ron Rosenfeld Feb 27 '18 at 10:56
  • Thanks for the clarification although is my understanding correct? `Index(Min_NDate,4,0)` will give `Min_NDate(5, :)` for my case right? – Pherdindy Feb 27 '18 at 11:05