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

 

[RoryA]

Where is the LoadLVDRun code located, and what does the ribbon callback look like?

 

[MartinS]

Where is the LoadLVDRun code located, and what does the ribbon callback look like?

Hi @RoryA
Sorry for the delay in responding, I didn't see the email and I'd found a workaround.

The code LoadLVDRun is shown in the example in my first post, i.e.

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

This is the Xml from the ribbon:

<button id="GetLVDBasisRibbonButton"
                        getLabel="rrf_btnGetLVDBasis_GetLabel"
                        onAction="rrf_btnGetLVDBasis_Click"
                        image="_x1_Get_LVD_Basis"
                        size="large"
                        getScreentip="rrf_btnGetLVDBasis_GetScreenTip"
                        getSupertip="rrf_btnGetLVDBasis_GetSuperTip"
                        getEnabled="rrf_btnGetLVDBasis_GetEnabled"/>

And these are the events that are called from the ribbon:

Sub rrf_btnGetLVDBasis_Click(irControl As IRibbonControl)
    'In my example, the following call would be 'TestProcess'
    GetLVDateFinalBasisRun
End Sub

'Get enabled/disabled - only enabled when on a specific tab and when a previous RRF hasn't already been imported
Sub rrf_btnGetLVDBasis_GetEnabled(irControl As IRibbonControl, ByRef returnedVal)
    returnedVal = StrComp(ActiveSheet.Name, gcstrVRRFTab, vbTextCompare) = 0 And _
                    Len(GetRefersToRange(gcstrLVDRRFRange).Value) = 0
End Sub

'Get label text
Sub rrf_btnGetLVDBasis_GetLabel(irControl As IRibbonControl, ByRef returnedVal)
    returnedVal = "Import LVD Final Run"
End Sub

'Get screen tip
Sub rrf_btnGetLVDBasis_GetScreenTip(irControl As IRibbonControl, ByRef returnedVal)
    returnedVal = "Import LVDate final basis run"
End Sub

'Get super tip
Sub rrf_btnGetLVDBasis_GetSuperTip(irControl As IRibbonControl, ByRef returnedVal)
    returnedVal = "Import the LVDate final basis run from last time's RRF"
End Sub

I ended up bringing the code from LoadLVDRun into my main process and that worked without issue. It was just really bizarre that it would not run when called.
Thanks
Martin

 

[RoryA]

Glad you have it sorted but what I asked was where the code was located (i.e. in which module), given that that routine was marked as Private?

 

[MartinS]

Glad you have it sorted but what I asked was where the code was located (i.e. in which module), given that that routine was marked as Private?

Sorry for the misunderstanding. It was in the same module as the main code.