Excel 2013-VBA runtime error 9 inside an UDF containing Advancedfilter statement but it's OK with debugging mode

Question

I do not understand why my UDF using AdvanceFilter works in debugging mode (F8) but provided with Error 9 - Subscript out of range when executed in normal mode. As if too rapid in execution (??) on runtime mode, not step by step.

Code sample of my UDF for more precision (gAitemCT is a user defined type) :

Public Function fG_PasteCTLinesOnSheetTmpFilteredDataset( _
  ByRef gL_CTLine_P As gAitemCT, ByRef intNbOcc As Integer) As String

 Dim sRet As String, sMsg As String
 Dim iLoop As Integer
 Dim iNbColMax As Integer
 Dim lNbRowMax As Long
 Dim sColWidth As Variant
 Dim rngInput As Range, rngOutput As Range
 Dim rngCriteria1 As Range
 Dim varTmp As Variant
 Dim iArrDime As Integer

 sRet = "OK"
 iLoop = 0

 On Error GoTo Diso

 '>> Constituer le Carré de datas <<
 Worksheets(G_sNameSRCPO).Select
 With Worksheets(G_sNameSRCPO)
   '>>
   lNbRowMax = .Cells(.Rows.Count, 5).End(xlUp).Row
   iNbColMax = .Cells(1, .Columns.Count).End(xlToLeft).Column
   '>>
 End With

 Worksheets(G_sNameSRCPO).Select
 '>> Defining INput range <<
  Set rngInput  = Worksheets(G_sNameSRCPO).Range("A1").Resize(lNbRowMax,iNbColMax)
  varTmp = salesWksheet.Range(Cells(1, 1)).Value

 '> Copy Header <
 '>> Setting up Criteria range(s) <<
  varTmp = Worksheets(G_sNameSRCPO).Cells(1, 1).Value

  Worksheets(G_sNameReferenceS).Select
  Worksheets(G_sNameReferenceS).Cells(1, 2).Select
  Worksheets(G_sNameReferenceS).Cells(1, 2).Value = varTmp

  ThisWorkbook.Save
  '**
  If (gL_CTLine_P.GsfPO <> 0) Then
  '>>
  'curWBook.Sheets("dataReferences").Activate
   Worksheets(G_sNameReferenceS).Cells(2, 2).Select
   Worksheets(G_sNameReferenceS).Cells(2, 2).Value = gL_CTLine_P.GsfPO
   Set rngCriteria1 = Worksheets(G_sNameReferenceS).Cells(1, 2).Resize(2, 1)
   '>>
  Else

  End If

  '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
  '>> Setting up the OUTput range <<
  Worksheets(G_sNameCTcurrentS).Select
  Set rngOutput = Worksheets(G_sNameCTcurrentS).Range("A1")

   '>>>>>>>>>>>>>>>>>>>>>>>>>
  '>> Pasting the Result <<<
  Worksheets(G_sNameSRCPO).Select
  rngInput.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=rngOutput, CriteriaRange:=rngCriteria1
  Worksheets(G_sNameCTcurrentS).Select

Veloma:
   '>>
   Set rngInput = Nothing
   Set rngCriteria1 = Nothing
   Set rngOutput = Nothing
   '>>
    intNbOcc = iArrDime
    '>>
    fG_PasteCTLinesOnSheetTmpFilteredDataset = sRet
    Exit Function
   '**
  Diso:

     Beep
     Beep
     sMsg = "PasteCTLinesOnSheetTmpFilteredDataset-ERR ::" & Err.Number & ":: - " & Err.Description
     Debug.Print sMsg
     sRet = sMsg
     Resume Veloma

  End Function

Thank you for any help.

Answer 1

You can't Select from a UDF. There are many things you can't do inside a UDF. A UDF is supposed to act on its parameters and return a result. Any side-effects to the workbook/worksheets/window are not allowed.

Instead of using Select, with use the With/End With construct, or assign the range to a range variable and refer to that.

You'll also have to figure out a different way of updating values that aren't the cell that's got the UDF in it. That's also not allowed in UDFs.