1

I am writing some VBA code intended to be compatible with Excel 2007 and newer versions. Since starting from Excel 2013 the Chart Series Filtering option and the related Chart.FullSeriesCollection object were introduced, I have included in my code an If statement to choose either this object or the older .SeriesCollection one depending on the Excel version.

However, VBA can't compile the code in Excel 2007 since .FullSeriesCollection is not defined. I wanted to try Late Binding so that the compiler skips the If statement that includes that undefined object, but Excel 2007 (with VBA version 6.3) doesn't recognize the Option Strict Off line either; I can only choose from Base, Compare, Explicit or Private to follow the Option statement.

How can I get the older VBA compiler to skip the line where .FullSeriesCollection is used? I have been learning VBA for 3 days, so excuse me if this is super obvious.

The relevant part of my code:

Private Sub EventChart_MouseDown(ByVal Button As Long, _
    ByVal Shift As Long, _
    ByVal x As Long, _
    ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long, Arg1b As Long
Dim myX As Variant, myY As Double
Dim xlVrsion As Integer, verSerColl As Object

xlVrsion = CInt(Left(Application.Version, 2)) 'Get Excel version and convert to an integer (2007 = 13.0; 2010 = 14.0; 2013 = 15.0; 2016 = 16.0)

With ActiveChart
    .GetChartElement x, y, ElementID, Arg1, Arg2

If ElementID = xlSeries Then 
  If xlVrsion >= 15 Then     'Check if Excel version is equal or newer than 2013.
    Set verSerColl = .FullSeriesCollection(Arg1)
  Else
    Set verSerColl = .SeriesCollection(Arg1)
  End If

'[More irrelevant code]
yuyu2809
  • 27
  • 1
  • 7

2 Answers2

3

You can probably use compiler constants such as

#If VBA7 Then     'Check if Excel version is equal or newer than 2013.
    Set verSerColl = .FullSeriesCollection(Arg1)
#Else
    Set verSerColl = .SeriesCollection(Arg1)
#End If
YowE3K
  • 23,852
  • 7
  • 26
  • 40
  • Thank you, it works perfectly! I wonder whether the `VBA7` constant, or rather, the VBA version, relates always to the Excel version or it may be updated without updating Excel itself. – yuyu2809 May 13 '17 at 11:50
  • 2
    @yuyu2809 VBA hasn't been significantly updated in a long time, so I don't think so. AFAIK, the only reason for VBA7 is because of 64-Bit Office https://msdn.microsoft.com/en-us/library/office/gg264421(v=office.14).aspx http://stackoverflow.com/questions/3072356/what-are-the-differences-between-vba-6-0-and-vba-7-0 – Slai May 13 '17 at 12:10
  • @yuyu2809 - The `VBA7` constant doesn't refer to the Excel version, it refers to a compatibility status. According to the [compller constants](https://msdn.microsoft.com/en-us/library/office/gg264614.aspx) link, VBA7 "Indicates that the development environment is Visual Basic for Applications, version 7.0 **compatible**." – YowE3K May 13 '17 at 18:29
0

You should be able to use late binding by casting the chart to Object or Variant:

If Val(Application.Version) >= 15 Then
    Dim objChart                 ' As Variant by default
    Set objChart = ActiveChart
    Set verSerColl = objChart.FullSeriesCollection(Arg1)
Else
    Set verSerColl = ActiveChart.SeriesCollection(Arg1)
End If

Just for example, this can run in older Office versions even if you can't compile it:

Dim verSerColl 
If Val(Application.Version) >= 15 Then
    Set verSerColl = ActiveChart.FullSeriesCollection(Arg1)
Else
    Set verSerColl = ActiveChart.SeriesCollection(Arg1)
End If
Slai
  • 22,144
  • 5
  • 45
  • 53
  • Thank you for your answer! It works perfectly, and I don't understand the negative vote(s) you got, but I've chosen YowE3K's answer because it seems more elegant to me (and also, he got here first! ;)). However, I'm curious as to why it works when setting `verSerColl` to `ActiveChart` and not to `.(Full)SeriesCollection`. Also, if you have time to look at it, I have another question for the same code here that hasn't been solved yet: [link](http://stackoverflow.com/questions/43942407/excel-vba-1004-error-with-series-formula-but-not-formulalocal) – yuyu2809 May 13 '17 at 11:42
  • I prefer the conditional compilation answer too as it allows early binding, but I don't think it will compile in Excel 2010 (which is probably irrelevant in your case). Not sure if I understand the question, but `Object.FullSeriesCollection` compiles because the type is not known at compile time, so even `Object.SomeMadeUpMethod` would compile – Slai May 13 '17 at 11:57
  • Well, as I said I've been learning VBA for a few days so I'm not sure how compiling works, but from the few things related to late binding I've read, I don't see why the compiler would "skip" your `Set verSerColl = objChart.FullSeriesCollection(Arg1)` but not `With ActiveChart [...] Set verSerColl = .FullSeriesCollection(Arg1)`. Also not sure what you mean by "the type is not known at compile time", but again, I may have to study more VBA before asking these questions (^_^;) – yuyu2809 May 13 '17 at 12:53
  • Also, why do you say it won't compile in Excel 2010? Is it because it uses VBA7 but doesn't include the `.FullSeriesCollection` object? In that case, since I'm not sure that the user won't use Excel 2010, I probably should use your late binding proposal for this case, right? – yuyu2809 May 13 '17 at 12:58
  • 1
    with `ActiveChart.FullSeriesCollection` the compiler can find that the type of `ActiveChart` is `Chart` and can check if that type has a `.FullSeriesCollection` method at compile time. If the compiler can't guess the actual type, then it will check if `objChart` has a `.FullSeriesCollection` method at run time when that line is executed. – Slai May 13 '17 at 13:22
  • 1
    VBA7 is Office 2010 and later and [`FullSeriesCollection`](https://msdn.microsoft.com/en-us/library/office/jj229516.aspx) Office 2013 and later, so it can't compile in Office 2010 but it can still run. VBA can run without being compiled. Some topics in the documentation might be helpful http://stackoverflow.com/documentation/excel-vba/topics – Slai May 13 '17 at 13:22