How to make excel vba code fully qualified / how to circumvent run-time error?

Question

I'm writing some VBA-code that dynamically sets the properties of some charts, like the data range, upon opening of my excel file. This is because the library I use to export Excel from my .Net project does not fully support chart properties.

I have 4 chart sheets and 1 data sheet in this file.

However, upon the first opening of the file, the following error is shown:

Run-Time Error: '-2147417848 (80010108)':

Automation
The object invoked has disconnected from its clients.

(also, upon the first opening, a warning is shown that the file is probably insecure and I manually have to allow opening, but I'm not sure if this has any relation to this problem)

Subsequent openings of this file do not trigger the error.

I searched on stackoverflow and in forums and found this Microsoft KB article

Based on the recommendations given there, I tried to make my code fully qualified (for example using the Dim app As Application and Dim wb As Workbook). However, this didn't solve my problem.

The offending line is marked with **

My questions are:

1. Did I fail to make some part of my code fully qualified?
2. Are there any other possible reasons for this error, and if so, how can they be solved?

My code (in the ThisWorkbook object):

Option Explicit
Private Sub Workbook_Open()

Dim app As Application
Set app = Excel.Application
Dim wb As Workbook
Set wb = app.ThisWorkbook

Dim lastRow As Long, lastRowString As String
lastRow = wb.Sheets("NameOfDatasheet").UsedRange.Row - 1 + Sheets("NameOfDatasheet").UsedRange.Rows.Count 'Worksheets("NameOfDatasheet").Range("A2:G41").AutoFilter field:=1, Criteria1:="<>"

With wb.Charts("NameOfChart1")
.SetSourceData Source:=wb.Sheets("NameOfDatasheet").Range("A2:A" & lastRow & ",D2:E" & lastRow)
'Styling type 1
.SeriesCollection(1).Border.Color = RGB(255, 0, 0)
.SeriesCollection(1).MarkerForegroundColor = RGB(255, 0, 0)
.SeriesCollection(1).MarkerBackgroundColor = RGB(255, 0, 0)
.SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle
.SeriesCollection(1).MarkerSize = 5
'Styling type 2
.SeriesCollection(2).Border.Color = RGB(0, 0, 255)
.SeriesCollection(2).MarkerForegroundColor = RGB(0, 0, 255)
.SeriesCollection(2).MarkerBackgroundColor = RGB(0, 0, 255)
.SeriesCollection(2).MarkerStyle = xlMarkerStyleNone
.SeriesCollection(2).MarkerSize = 5
End With

With wb.Charts("NameOfChart2")
.SetSourceData Source:=wb.Sheets("NameOfDatasheet").Range("A2:A" & lastRow & ",H2:I" & lastRow)
'Styling type 1
.SeriesCollection(1).Border.Color = RGB(255, 0, 0)
.SeriesCollection(1).MarkerForegroundColor = RGB(255, 0, 0)
.SeriesCollection(1).MarkerBackgroundColor = RGB(255, 0, 0)
.SeriesCollection(1).MarkerStyle = xlMarkerStyleCircle
.SeriesCollection(1).MarkerSize = 5
'Styling type 2
.SeriesCollection(2).Border.Color = RGB(0, 0, 255)
.SeriesCollection(2).MarkerForegroundColor = RGB(0, 0, 255)
.SeriesCollection(2).MarkerBackgroundColor = RGB(0, 0, 255)
.SeriesCollection(2).MarkerStyle = xlMarkerStyleNone
.SeriesCollection(2).MarkerSize = 5
End With

Dim MaxVal As Variant, MinVal As Variant

With wb.Charts("NameOfChart3")
.SetSourceData Source:=wb.Sheets("NameOfDatasheet").Range("A2:A" & lastRow & ",F2:F" & lastRow)
MaxVal = app.Max(wb.Sheets("NameOfDatasheet").Range("G2:G" & lastRow))
MinVal = app.Min(wb.Sheets("NameOfDatasheet").Range("G2:G" & lastRow))
If (MinVal = MaxVal) Then
    MinVal = 0
End If
MaxVal = MaxVal + 0.1
MinVal = MinVal - 0.1
.Axes(xlValue).MinimumScale = MinVal
.Axes(xlValue).MaximumScale = MaxVal
End With

With wb.Charts("NameOfChart4")
**.SetSourceData Source:=wb.Sheets("NameOfDatasheet").Range("A2:A" & lastRow & ",B2:B" & lastRow)**
MaxVal = app.Max(wb.Sheets("NameOfDatasheet").Range("C2:C" & lastRow))
MinVal = app.Min(wb.Sheets("NameOfDatasheet").Range("C2:C" & lastRow))
If (MinVal = MaxVal) Then
    MinVal = 0
End If
MaxVal = MaxVal + 0.1
MinVal = MinVal - 0.1
.Axes(xlValue).MinimumScale = MinVal
.Axes(xlValue).MaximumScale = MaxVal
End With

End Sub

Answer 1

A couple of things to consider...why aren't you using the built in Worksheets object, it's cleaner than the Sheets collection? Also if the worksheet in Question is not 'first ' it has to have Activate called before you can access fields. (depending on the version of Excel, it may need to be activated in any case so I would recommend doing it regardless) you can do this:

wb.Sheets("nameOfWorksheet").Activate

Or

Worksheets("nameOfWorksheet").Activate

Just a note here, you do a lot of repetitive calls into collections. You can't count on the compiler to optimize that for you. Every collection.get(), especially one where a string is resolved to an index number eats cycles. It's better to grab a reference and access the worksheet by that pointer and release it when done.