Code Refuses to Run Procedure Call

[MartinS]

Really odd one...
I have a procedure called from a Ribbon click event, and it works except for one line which is a call (one of several) to a sub which copies data from an array into a range. Apparently you can't put a breakpoint directly on the call, but even with a breakpoint on the first line of the procedure, it never hits it, and also never errors, so the following code fails as this step isn't completing.
This is the format:

    Set rngRunTable = ThisWorkBook.Names("RunTable").RefersToRange
    'Get the index of the first row
    intRunCount = LBound(rngRunTable.Value, 1)
    'Load the data into the run table
    LoadLVDRun intFinalIndex, rngRunTable, intRunCount

And this is that procedure which is being skipped by the code:

Private Sub LoadLVDRun(intFinalIndex As Integer, rngRunTable As Range, intLVDIndex As Integer)
Dim i                   As Integer
Dim varLVDEAColumns()   As Variant
Const cintLVDColumns As Integer = 8
varLVDEAColumns = Array(1, 2, 3, 6, 7)
    For i = LBound(varLVDEAColumns) To UBound(varLVDEAColumns) Step 1
        rngRunTable.Cells(intLVDIndex, varLVDEAColumns(i)).Value = gvarLVDRunTable(intLVDIndex, varLVDEAColumns(i))
    Next i
    For i = cintLVDColumns To UBound(gvarLVDRunTable, 2) Step 1
        rngRunTable.Cells(intLVDIndex, i).Value = gvarLVDRunTable(intFinalIndex, i)
    Next i
End Sub

The variable 'gvarLVDRunTable' is a variant array that contains the data from another workbook (opened earlier in the code) and at the point of the call, it contains data, as do the other arguments. In this example, both intFinalIndex and intLVDIndex are 1.
I've never seen this happen before, and have been developing with Excel for many years, so am at a loss as to why it's happening.
Pulling the code out into a clean workbook, and restricting it to just the relevant code, it works as expected:

Dim varLVDRunTable As Variant

Sub TestProcess()
Dim intRunCount     As Integer
Dim rngRunTable     As Range
    'Get the run table into an array
    varLVDRunTable = ThisWorkbook.Names("RunTableSource").RefersToRange.Value
    'Get the run table range object
    Set rngRunTable = ThisWorkbook.Names("RunTableTarget").RefersToRange
    'Get the index of the first row
    intRunCount = LBound(rngRunTable.Value, 1)
    'Load the data into the run table
    LoadLVDRun 1, rngRunTable, intRunCount
End Sub

Private Sub LoadLVDRun(intFinalIndex As Integer, rngRunTable As Range, intLVDIndex As Integer)
'Declare procedure level variables
Dim i                   As Integer
Dim varLVDEAColumns()   As Variant
'Define the columns that come from the LVD final basis row
Const cintLVDColumns As Integer = 8
'Specify the columns to copy from LVDate basis
varLVDEAColumns = Array(1, 2, 3, 6, 7)
    'Populate data from the LVD final basis row
    For i = LBound(varLVDEAColumns) To UBound(varLVDEAColumns) Step 1
        rngRunTable.Cells(intLVDIndex, varLVDEAColumns(i)).Value = varLVDRunTable(intLVDIndex, varLVDEAColumns(i))
    Next i
    'Populate data from the LVD final run
    For i = cintLVDColumns To UBound(varLVDRunTable, 2) Step 1
        rngRunTable.Cells(intLVDIndex, i).Value = varLVDRunTable(intFinalIndex, i)
    Next i
End Sub

Any ideas?
Thanks in advance
Martin

 

[Micron]

Apparently you can't put a breakpoint directly on the call

Well I can, so I don't understand that. Hate to ask, but did you step through and check all your variables as you go? You never disabled any application or wb level events prior to executing this code?

 

[MartinS]

Well I can, so I don't understand that. Hate to ask, but did you step through and check all your variables as you go? You never disabled any application or wb level events prior to executing this code?

When investigating online I'd found plenty of posts saying you can't but was sure I'd done it in the past, and I've just tried it and it does allow me to put a breakpoint on the call - odd!
Yes, absolutely checked the variables, and no, no application or workbook level events have been disabled.
Here's the locals window at the breakpoint of the call:

Thanks
Martin

 

[Alex Blakenburg]

even with a breakpoint on the first line of the procedure, it never hits it, and also never errors, so the following code fails as this step isn't completing.

Do you have On Error statement before this Call line ?
It is erroring out for me.

I needed to set the 2 integer variables to ByVal to get it to not error out.

Private Sub LoadLVDRun(ByVal intFinalIndex As Integer, rngRunTable As Range, ByVal intLVDIndex As Integer)

 

[MartinS]

Do you have On Error statement before this Call line ?
It is erroring out for me.

I needed to set the 2 integer variables to ByVal to get it to not error out.

Private Sub LoadLVDRun(ByVal intFinalIndex As Integer, rngRunTable As Range, ByVal intLVDIndex As Integer)

Hi Alex. No, no on error at all, i try not to use them unless absolutely necessary.
That's interesting that it doesn't error for me, but does for you. I'll try with the ByVal keywords and see what happens.
Thanks
Martin

 

[MartinS]

Hi Alex. No, no on error at all, i try not to use them unless absolutely necessary.
That's interesting that it doesn't error for me, but does for you. I'll try with the ByVal keywords and see what happens.
Thanks
Martin

Didn't change anything adding ByVal.
I've ended up simply moving the few lines of code from LoadLVDRun into the process so it's not called and that works perfectly, i.e.

'    'Load the data into the run table
'    LoadLVDateFinalRun intFinalIndex, rngRunTable, intRunCount
    'Populate data from the LVD final basis row
    For i = LBound(varLVDEAColumns) To UBound(varLVDEAColumns) Step 1
        rngRunTable.Cells(intRunCount, varLVDEAColumns(i)).Value = gvarLVDRunTable(intRunCount, varLVDEAColumns(i))
    Next i
    'Populate data from the LVD final run
    For i = cintLVDColumns To UBound(gvarLVDRunTable, 2) Step 1
        rngRunTable.Cells(intRunCount, i).Value = gvarLVDRunTable(intFinalIndex, i)
    Next i

Most odd

 

[Alex Blakenburg]

I just discovered I had to put the ByVal because of a mismatch of the data type.
How have you defined the 2 int variables in main sub ?

 

[MartinS]

I just discovered I had to put the ByVal because of a mismatch of the data type.
How have you defined the 2 int variables in main sub ?

Both as Integer:

Dim intFinalIndex       As Integer
Dim intRunCount         As Integer

 

[Alex Blakenburg]

I didn't have the gvars set up so I commented them out but I stepped into the code fine.

 

[MartinS]

It also worked for me also using my cut-down code in a new workbook but doesn't in it's original structure/workbook.
Thanks
Martin