Runtime error 9 "subscript out of range"

[aliy2k4u]

Hello everyone!

Problem:– runtime error 9, “subscript out of range” - everywhere!
Sub MacroRefresh(Control As IRibbonControl)
Dim lRet As Long
Dim pc As PivotCache
Dim pivotTableExists

pivotTableExists = False
For Each pc In ActiveWorkbook.PivotCaches
pivotTableExists = True
Next

If Not pivotTableExists Then
GoTo No_Pivot_Table
End If

'EA: Refresh on Custom/non Wellbase reports gives exception.
Dim propertySheet As Worksheet
On Error Resume Next
Set propertySheet = Sheets("Properties")
Dim sql As String

If Not propertySheet Is Nothing Then
sql = propertySheet.Cells(4, 2).value
End If

If propertySheet Is Nothing Or Len(sql) <= 0 Then
MsgBox "Cannot refresh this pivot report." & vbNewLine & "You can only use this feature with reports generated from WellBase.", vbOKOnly, "Caution!"
Exit Sub
End If

Public Function ParseXMLTemplate(filename As Variant) As Boolean

Dim lngIndex As Long
Dim XDoc As MSXML2.DOMDocument
Dim xmlRoot As Object
Dim xTemplates As MSXML2.IXMLDOMNodeList, template As MSXML2.IXMLDOMElement
Dim xColumns As MSXML2.IXMLDOMNodeList, column As MSXML2.IXMLDOMElement
Dim xRows As MSXML2.IXMLDOMNodeList, row As MSXML2.IXMLDOMElement
Dim xFilters As MSXML2.IXMLDOMNodeList, filter As MSXML2.IXMLDOMElement
Dim xValues As MSXML2.IXMLDOMNodeList, value As MSXML2.IXMLDOMElement
Dim propertySheet As Worksheet

On Error GoTo Errhandler:

If Len(filename) = 0 Then
ParseXMLTemplate = False
Exit Function
End If

I am using Excel 2016.

Macros tried available upon request.

Can anyone help?

Best regards,

[shg]

Welcome to the board.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.