Recall from post 2: "Variable datatypes must be explicitly declared else they default to Variant. So only the last variable is declared as Object." So in the code:
Dim a, b As Range
Dim MinVal, MaxVal As Double
Only the second variable on each line is explicitly declared, the first ones default to Variant. Code will probably still work, but it is a fine point to be aware of.
Following runs without error. I see the chart is modified, even with my data.
Code:
Sub cmbexport_toexcel_Click()
Dim xl As Excel.Application, wb As Excel.Workbook, ws As Excel.Worksheet, xlch As Excel.ChartObject
Dim a As Excel.Range, b As Excel.Range
Dim sExcelWB As String
Dim MinVal As Double, MaxVal As Double
On Error Resume Next
Set xl = CreateObject("excel.application")
Err.Clear
On Error GoTo 0
sExcelWB = CurrentProject.Path & "\qry_123.xlsx"
xl.Visible = True
xl.UserControl = True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_123", sExcelWB
Set wb = xl.Workbooks.Open(sExcelWB)
Set ws = wb.Sheets("qry_123")
ws.Columns.AutoFit
ws.Columns("B:C").HorizontalAlignment = xlCenter
ws.Shapes.AddChart.Select
Set xlch = ws.ChartObjects(1)
With xlch
.RoundedCorners = True
With .Chart
.ChartType = xlBarStacked
.HasTitle = True
With .ChartTitle
.Text = "Title"
With .Font 'begin font
.Name = "Arial"
.Size = 14
.FontStyle = "bold"
End With ''end font
End With 'end chart title
.HasLegend = False
' begin set source data
.SeriesCollection.NewSeries
.SeriesCollection(1).Values = ws.Range("A2", ws.Range("A2").End(xlDown))
.SeriesCollection(1).XValues = ws.Range("C2", ws.Range("C2").End(xlDown))
.SeriesCollection.NewSeries
.SeriesCollection(2).Values = ws.Range("D2", ws.Range("D2").End(xlDown))
.Axes(xlCategory).ReversePlotOrder = True
'Set axis min-max values
Set a = ws.Range("A2:A" & Rows.Count)
Set b = ws.Range("A2:B" & Rows.Count)
MinVal = WorksheetFunction.Min(a)
MaxVal = WorksheetFunction.Max(b)
.Axes(xlValue).MinimumScale = MinVal
.Axes(xlValue).MaximumScale = MaxVal
End With 'end .Chart
End With 'end xlch
'wb.Save
Set ws = Nothing
Set wb = Nothing
End Sub