Convert Excel VBA 2016 backwards to Excel VBA 2010

M

McCITech

New Member
Joined
Dec 27, 2016
Messages
39
Good afternoon,

I'm creating some templates for use by multiple users on multiple PC's.
The PC I use to create the code is running Excel 2016.
Some of the PCs used by other users are running Excel 2010, and Excel 2013.

The issue: When I program on my PC (2016), everything works fine. When the users run the template on their machine (2010 & 2013), the code crashes due to incompatibility issues.
"Compile Error - incompatible version"

The Chart Object that is giving me a headache is already embedded in the worksheet, it simply updates with new information each time the template is used.

Here is the code, can someone help me make it work on all versions, please??

VBA Code: 
Sub Graph_Control()

On Error Resume Next

Select Case ThisWorkbook.Worksheets("Cal Sheet").ComboBox8

Case Is = ThisWorkbook.Worksheets("Cal Sheet").ComboBox8.List(0)



DeleteSeries

ScaleAxes

Graph_Style

Scatter_Series

CenterLabels



End Select

End Sub
'##############################################################
Private Sub DeleteSeries()

On Error Resume Next

ThisWorkbook.Worksheets("CERT").ChartObjects("Chart 1").Activate

  Dim iSrs As Long
  
  With ActiveChart
    For iSrs = .SeriesCollection.Count To 1 Step -1
      If InStr(LCase$(.SeriesCollection(iSrs).Name), "series") > 0 Then
        .SeriesCollection(iSrs).Delete
      End If
    Next
  End With
End Sub

'#############################################################
Private Sub ScaleAxes()

On Error Resume Next

ThisWorkbook.Worksheets("CERT").ChartObjects("Chart 1").Activate

    
Dim x1Scale As Variant, MajorLine As Variant, MinorLine As Variant
Dim x2Scale As Variant, Capacity As Variant
Dim y1Scale As Variant
Dim y2Scale As Variant


x1Scale = ThisWorkbook.Worksheets("Cal Sheet").Cells(40, "H").Value 'Bottom Axis
x2Scale = ThisWorkbook.Worksheets("Cal Sheet").Cells(48, "H").Value
y1Scale = ThisWorkbook.Worksheets("Cal Sheet").Cells(40, "T").Value 'Left Axis
y2Scale = ThisWorkbook.Worksheets("Cal Sheet").Cells(48, "T").Value + (y1Scale / 2)


Capacity = ThisWorkbook.Worksheets("Cal Sheet").Cells(11, "C").Value

Select Case Capacity

Case Is < 1000

MajorLine = 50
MinorLine = 10

Case Is < 1500

MajorLine = 100
MinorLine = 20

Case Is < 3500

MajorLine = 150
MinorLine = 30

Case Is > 5000

MajorLine = 200
MinorLine = 50

Case Else

MajorLine = 500
MinorLine = 100

End Select

With ActiveSheet.ChartObjects("Chart 1").Chart.ChartArea.Format.TextFrame2.TextRange.Font
    .Name = "Arial"
    .Size = 14
    .Bold = msoTrue
End With
    
  With ActiveChart.Axes(xlCategory)
    .MaximumScale = x2Scale
    .MinimumScale = x1Scale
    .MinorUnit = 5
    .MajorUnit = 25
    .Crosses = xlAutomatic
  End With

ActiveChart.Axes(xlValue).Select

  With ActiveChart.Axes(xlValue)
    .MaximumScale = y2Scale
    .MinimumScale = y1Scale
    .MajorUnit = MajorLine
    .MinorUnit = MinorLine
    .Crosses = xlAutomatic
    .TickLabels.NumberFormat = "#"
  End With

ActiveChart.ChartArea.Select

End Sub
'#############################################################
Private Sub Graph_Style()

Select Case ThisWorkbook.Worksheets("Cal Sheet").ComboBox9

Case Is = ThisWorkbook.Worksheets("Cal Sheet").ComboBox9.List(0)
Graph_French

Case Is = ThisWorkbook.Worksheets("Cal Sheet").ComboBox9.List(1)
Graph_English

End Select

End Sub

'#############################################################
Private Sub Graph_French()

On Error Resume Next

Dim Units1 As Variant
Dim Units2 As Variant

ThisWorkbook.Worksheets("CERT").ChartObjects("Chart 1").Activate

ActiveChart.ChartArea.Select

PressureValue = ThisWorkbook.Worksheets("Cal Sheet").ComboBox12.Value

Set DataSeries = ActiveChart.SeriesCollection.NewSeries

        DataSeries.XValues = ThisWorkbook.Worksheets("Cal Sheet").Range("H40, H42, H44, H46, H48")
        DataSeries.Values = ThisWorkbook.Worksheets("Cal Sheet").Range("T40, T42, T44, T46, T48")
        
            With ActiveChart.FullSeriesCollection(1).Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 0, 255)
                .Transparency = 0
            End With
            
        With ActiveChart.Axes(xlValue)
            Units1 = ThisWorkbook.Worksheets("Cal Sheet").Cells(10, "C").Text
        
             .HasTitle = True
         With .AxisTitle
             .Caption = "Couple en " & Units1
        ActiveChart.Axes(xlValue).AxisTitle.Select
            With Selection.Format.TextFrame2.TextRange.Font
                .BaselineOffset = 0
                .Size = 14
                .Bold = msoTrue
                .Name = "Arial"
            End With
         End With
        End With
            
        With ActiveChart.Axes(xlCategory)
            Units2 = PressureValue
             .HasTitle = True
         With .AxisTitle
             .Caption = "Pression Hydraulique en " & Units2
        ActiveChart.Axes(xlCategory).AxisTitle.Select
            With Selection.Format.TextFrame2.TextRange.Font
                .BaselineOffset = 0
                .Size = 14
                .Bold = msoTrue
                .Name = "Arial"
            End With
         
         End With
         
        End With
   
ActiveChart.ChartArea.Select

End Sub
'#############################################################
Private Sub Graph_English()

On Error Resume Next

Dim Units1 As Variant
Dim Units2 As Variant

ThisWorkbook.Worksheets("CERT").ChartObjects("Chart 1").Activate

ActiveChart.ChartArea.Select

PressureValue = ThisWorkbook.Worksheets("Cal Sheet").ComboBox12.Value

Set DataSeries = ActiveChart.SeriesCollection.NewSeries

    
        DataSeries.XValues = ThisWorkbook.Worksheets("Cal Sheet").Range("H40, H42, H44, H46, H48")
        DataSeries.Values = ThisWorkbook.Worksheets("Cal Sheet").Range("T40, T42, T44, T46, T48")
        
            With ActiveChart.FullSeriesCollection(1).Format.Line
                .Visible = msoTrue
                .ForeColor.RGB = RGB(0, 0, 255)
                .Transparency = 0
            End With
            
        With ActiveChart.Axes(xlValue)
            Units1 = ThisWorkbook.Worksheets("Cal Sheet").Cells(10, "C").Text
        
             .HasTitle = True
         With .AxisTitle
             .Caption = "Torque in " & Units1
        ActiveChart.Axes(xlValue).AxisTitle.Select
            With Selection.Format.TextFrame2.TextRange.Font
                .BaselineOffset = 0
                .Size = 14
                .Bold = msoTrue
                .Name = "Arial"
            End With
         End With
        End With
            
        With ActiveChart.Axes(xlCategory)
           
            Units2 = PressureValue
            
             .HasTitle = True
         With .AxisTitle
             .Caption = "Hydraulic Pressure in " & Units2
        ActiveChart.Axes(xlCategory).AxisTitle.Select
            With Selection.Format.TextFrame2.TextRange.Font
                .BaselineOffset = 0
                .Size = 14
                .Bold = msoTrue
                .Name = "Arial"
            End With
         
         End With
         
        End With


ActiveChart.ChartArea.Select

End Sub
'#############################################################

Private Sub Scatter_Series()

On Error Resume Next

Dim CalSheet As Worksheet, Cert As Worksheet, PreCert As Variant, OldNew As Variant

Set CalSheet = ThisWorkbook.Worksheets("Cal Sheet")
Set Cert = ThisWorkbook.Worksheets("CERT")
PreCert = ThisWorkbook.Worksheets("Cal Sheet").Cells(13, "C").Value
OldNew = ThisWorkbook.Worksheets("Cal Sheet").Cells(26, "A").Value

If PreCert <> "" Then

ThisWorkbook.Worksheets("CERT").ChartObjects("Chart 1").Activate

ActiveChart.ChartArea.Select

ActiveChart.SeriesCollection.NewSeries

    
        ActiveChart.FullSeriesCollection(2).ChartType = xlXYScatterSmooth
        
    If OldNew <> "" Then
    
        ActiveChart.SeriesCollection(2).Values = ThisWorkbook.Worksheets("Cal Sheet").Range("C24, C26, C28, C30, C32")
        ActiveChart.SeriesCollection(2).XValues = ThisWorkbook.Worksheets("Cal Sheet").Range("F24, F26, F28, F30, F32")
    Else
        ActiveChart.SeriesCollection(2).Values = ThisWorkbook.Worksheets("Cal Sheet").Range("C24, C28, C32")
        ActiveChart.SeriesCollection(2).XValues = ThisWorkbook.Worksheets("Cal Sheet").Range("F24, F28, F32")
    End If
    
            With ActiveChart.FullSeriesCollection(2).Format.Line
                .Visible = msoTrue
                .DashStyle = msoLineDash
                .ForeColor.RGB = RGB(255, 0, 0)
                .Transparency = 0.5
                .Weight = 1.5
            End With

End If


ActiveChart.ChartArea.Select

End Sub
'#############################################################

Private Sub CenterLabels()

On Error Resume Next

ThisWorkbook.Worksheets("CERT").ChartObjects("Chart 1").Activate

Dim ch As ChartObject, Location As Variant, Capacity As Variant, PreCert As Variant
Set ch = ActiveSheet.ChartObjects("Chart 1")

Location = ThisWorkbook.Worksheets("Cal Sheet").Cells(48, "T").Value
Capacity = ThisWorkbook.Worksheets("Cal Sheet").Cells(11, "C").Value
PreCert = ThisWorkbook.Worksheets("Cal Sheet").Cells(13, "C").Value

Select Case Location

    Case Is > Capacity
    
    With ch.Chart
        .ApplyDataLabels xlDataLabelsShowValue
        .SeriesCollection(1).DataLabels.Position = xlLabelPositionBelow
    End With
    
    Case Is < Capacity
    
    With ch.Chart
        .ApplyDataLabels xlDataLabelsShowValue
        .SeriesCollection(1).DataLabels.Position = xlLabelPositionAbove
    End With

End Select

If PreCert <> "" Then
 
    ActiveChart.FullSeriesCollection(2).Select
    ActiveChart.SetElement (msoElementDataLabelNone)
End If

    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).Points(1).DataLabel.Delete
   
ActiveChart.ChartArea.Select

End Sub

Thank you for any help you can give!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
FullSeriesCollection wasn't available in 2010 - use Seriescollection instead.
 
Upvote 0
Solution
When writing code that is going to be used on multiple versions of Excel, it usually best practice/recommended to write the code on the oldest version of Excel that will be using it (Excel 2010, in your example).

If you are able to do that, you will greatly decrease the chances of people running into problems of code not working when running it on different versions (occasionally, they may need to select a missing library reference, but it should be available for them). If you write the code in the newer versions, you run the risk of using newer functionality that the people using the older versions won't have available to them (as you experienced).
 
Upvote 0
RoryA - Thank you very much for pin pointing the compatibility issue in this code. Removing the FULL from the SeriesCollection worked like a charm!

Would you be able to look this code over again and attempt to determine why my Y-Axis grid values are not being displayed at the bottom of the chart when using 2010 / 2013 Excel?

Thank you for your help with this.
 
Upvote 0
Seems I may have figured it out on my own.

Unless I'm mistaken, in Excel 2016 when you use ActiveChart.Axes(xlCategory), it uses xlPrimary by default, but older versions of Excel require it to be explicit: ActiveChart.Axes(xlCategory, xlPrimary)

That being the case or not, explicitly declaring xlPrimary appears to have done the trick.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
This Macro doesn't add vertical axes title
Replies
0
Views
435
yabi100
Y
S
  • Question Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
900
Steve48
S
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
T
  • Question Question
Excel VBA/Macro Error
Replies
1
Views
1K
mart37
M
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
348
dilshod_k
D

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top