Solved

prevent compilation of specific lines of code

Posted on 2014-02-02
6
392 Views
Last Modified: 2014-02-08
hey guys, i've got this code in my Access project

    Select Case xlsApp.Version
        Case "11.0" 'excel 2003
            xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & xlsWst.Range("A65000").End(xlUp).Row).Select
            xlsApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        Case "12.0" 'excel 2007
            xlsWst.Sort.SortFields.Clear
            xlsWst.Sort.SortFields.Add Key:=xlsWst.Range("A2:A" & xlsWst.Range("A65000").End(xlUp).Row), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With xlsWst.Sort
                .SetRange xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & _
                    xlsWst.Range("A65000").End(xlUp).Row)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
    End Select

Open in new window


some computers have Microsoft Office 2003 and some have 2007. that's why i would like to have have this switch in my code. however because the Excel Object Library reference in my Access project at any one time is either Excel 2003 or Excel 2007, when i try and compile my project or run it, it won't work - rightly so. "Compile error: Method or data member no found"

Question: how can i get the compiler to not compile these lines of code and just run it in runtime?

Homework: i understand i can develop in early binding and change to late binding before deployment as per http://stackoverflow.com/questions/9491314/preventing-excel-vba-compile-errors-due-to-users-having-an-older-version-of-ms-o but i'm wondering if i can just skip those lines of compilation with a # key or something like that - some compiler specific language / signals. thanks guys!! = ))
0
Comment
Question by:developingprogrammer
  • 4
  • 2
6 Comments
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 39828660
Use conditional compilation.  Here is some code I use to swap between early and late binding:
#Const ExcelRef = 0
#If ExcelRef = 0 Then ' Late binding
    Dim appExcel As Object     'Excel Object
    Dim wbkNew As Object    'Workbook Object
    Dim wksNew As Object    'Sheet Object
    Dim wbkTemplate As Object   'Workbook Object for Template

    Set appExcel = CreateObject("Excel.Application")
    ' Remove the Excel reference if it is present   -   <=======
    On Error Resume Next
    Set ref = References!Excel
    If Err.Number = 0 Then
        References.Remove ref
    ElseIf Err.Number <> 9 Then 'Subscript out of range meaning not reference not found
        MsgBox Err.Description
        Exit Sub
    End If
' Use your own error handling label here
On Error GoTo FormatWeeklyJobStatus_Error
#Else
    ' a reference to MS Excel <version number> Object Library must be specified
    Dim appExcel As Excel.Application      'Excel Object
    Dim wbkNew As Excel.Workbook        'Workbook Object
    Dim wksNew As Excel.Worksheet       'Sheet Object
    Dim wbkTemplate As Excel.Workbook   'Workbook Object for Template

    Set appExcel = New Excel.Application
#End If

Open in new window

The same technique should work for swapping versions.
0
 

Author Comment

by:developingprogrammer
ID: 39828718
hi Pat! thanks for your help!

i did quite a bit of reading and i think my situation may be a bit unique, or perhaps i am just not seeing it at the moment, could you help me out?

1) i am in Access.
2) i open Excel through code.
3) i would like to find out which version of Excel i'm opening. i do this through Applicatoin.Version
4a) if it's Excel 2003, i'll compile the Excel 2003 code.
4b) if it's Excel 2007, i'll compile the Excel 2007 code.

the problem come when i've got both Microsoft Office 2003 and 2007 on my computer.

1) i'm using Access 2007 to open my mdb.
2) my mdb's Excel reference is Excel 2003 --> because by compiling with Excel 2003 reference, it will work on both Excel 2003 and 2007
3) i want to use conditional compilation
4) the problem is that conditional compilation requires a compiler constant

i don't want to set the compiler constant manually. i would like to set it at run time based on a condition. something along the lines of
If xlsApp.Version = "12.0" Then
    #Const ExcelVersion = "2003"
ElseIf xlsApp.Version = "11.0" Then
    #Const ExcelVersion = "2007"
End If

Open in new window


if i can strip away the compiler constant even better (i've tried and tested but can't achieve that)


Question 1: how can set my compiler constant based on a condition?
Question 2: is it possible to use conditional compilation without a compiler constant? something like the below


#If xlsApp.Version = "12.0" Then
    Stop
#ElseIf xlsApp.Version = "11.0" Then
    Stop
#End If

Open in new window


#If Val(xlsApp.Version) = "12.0" Then
    Stop
#ElseIf Val(xlsApp.Version) = "11.0" Then
    Stop
#End If

Open in new window


thanks in advance Pat and everyone who helps! = )
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 39828754
You might need to move the code to a library database.  You would have two.  One for each Excel version.  Then depending on the version of Excel, call subX or subY.  But first, I would try using a variable rather than the constant in my example.  I have other code that deals with 64-bit vs 32-bit API calls and it works with a variable.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Assisted Solution

by:developingprogrammer
developingprogrammer earned 0 total points
ID: 39828770
thanks Pat! i tried using a variable but it didn't work. i came up with a solution and was looking forward to share with you = )

i used nested conditional compilation in the end

so i have 2 compiler constants - one for early or late binding, and the other one for 2003 or 2007. so in development i would use early + 2003 or 2007 and when i'm rolling out i'll change it to late. whilst this is not as dynamic as the code library database (which i've always wanted to do ha but i'm not full time on this so can't invest too much effort), it helps me keep everything relatively simple

here's my code - note that in my code i joined the 2 compiler constants into 1 so the code is neater

#Const ExcelBindingAndVersion = "Early2003" '"Early2003" or "Early2007" or "Late"

    'engage worksheet for import
        'i'm using conditional compilation here for early and late binding _
            i'm also aligning it to the develpoment version later
        #If ExcelBindingAndVersion = "Early2003" Or ExcelBindingAndVersion = "Early2007" Then
            Stop
            'early binding for 2003 and 2007
            Dim xlsApp                      As Excel.Application
            Dim xlsWbk                      As Excel.Workbook
            Dim xlsWst                      As Excel.Worksheet
            Set xlsApp = New Excel.Application
        #ElseIf ExcelBindingAndVersion = "Late" Then
            Stop
            'late binding
            Dim xlsApp                      As Object 'excel object
            Dim xlsWbk                      As Object 'excel object
            Dim xlsWst                      As Object 'excel object
            Set xlsApp = VBA.CreateObject("Excel.Application")
        #End If
    

Open in new window


this is continued later on in my function with

        'sorting to put empty rows on top
        #If ExcelBindingAndVersion = "Early2003" Then
            'early binding for excel 2003
            xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & xlsWst.Range("A65000").End(xlUp).Row).Select
            xlsApp.Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        #ElseIf ExcelBindingAndVersion = "Early2007" Then
            'early binding for excel 2007
            xlsWst.Sort.SortFields.Clear
            xlsWst.Sort.SortFields.Add Key:=xlsWst.Range("A2:A" & xlsWst.Range("A65000").End(xlUp).Row), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With xlsWst.Sort
                .SetRange xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & _
                    xlsWst.Range("A65000").End(xlUp).Row)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        #End If

Open in new window

0
 

Accepted Solution

by:
developingprogrammer earned 0 total points
ID: 39829923
final code looks something like this

    #Const ExcelBindingAndVersion = "Early2007" '"Early2003" or "Early2007" or "Late"
    'engage worksheet for import
        'i'm using conditional compilation here for early and late binding _
            i'm also aligning it to the develpoment version later
        'note if VBA reference is Excel 2003 but default Excel programme is Excel 2007, _
            change default to 2003. that's the only way to test
        #If ExcelBindingAndVersion = "Early2003" Or ExcelBindingAndVersion = "Early2007" Then
            'early binding for 2003 and 2007
            Dim xlsApp                      As Excel.Application
            Dim xlsWbk                      As Excel.Workbook
            Dim xlsWst                      As Excel.Worksheet
            Set xlsApp = New Excel.Application
        #ElseIf ExcelBindingAndVersion = "Late" Then
            'late binding
            Dim xlsApp                      As Object 'excel object
            Dim xlsWbk                      As Object 'excel object
            Dim xlsWst                      As Object 'excel object
            Set xlsApp = VBA.CreateObject("Excel.Application")
        #End If

'do other things here


 'sorting to put empty rows on top
        #If ExcelBindingAndVersion = "Early2003" Then
            'early binding for excel 2003
            xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & xlsWst.Range("A65000").End(xlUp).Row).Select
            xlsApp.Selection.Sort Key1:=xlsWst.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                DataOption1:=xlSortNormal
        #ElseIf ExcelBindingAndVersion = "Early2007" Then
            'early binding for excel 2007
            xlsWst.Sort.SortFields.Clear
            xlsWst.Sort.SortFields.Add Key:=xlsWst.Range("A2:A" & xlsWst.Range("A65000").End(xlUp).Row), _
                SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With xlsWst.Sort
                .SetRange xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & _
                    xlsWst.Range("A65000").End(xlUp).Row)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        #ElseIf ExcelBindingAndVersion = "Late" Then

            Select Case xlsApp.Version
                Case "11.0" 'excel 2003
                    xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & xlsWst.Range("A65000").End(xlUp).Row).Select
                    xlsApp.Selection.Sort Key1:=xlsWst.Range("A2"), Order1:=xlAscending, Header:=xlYes, _
                        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal
                Case "12.0"
                    xlsWst.Sort.SortFields.Clear
                    xlsWst.Sort.SortFields.Add Key:=xlsWst.Range("A2:A" & xlsWst.Range("A65000").End(xlUp).Row), _
                        SortOn:=0, Order:=1, DataOption:=0
                        'xlSortOnValues=0
                        'xlAscending=1
                        'xlSortNormal=0

                    With xlsWst.Sort
                        .SetRange xlsWst.Range("A1:" & ConvertToLetter(xlsWst.Cells(1, 100).End(xlToLeft).Column) & _
                            xlsWst.Range("A65000").End(xlUp).Row)
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
    
        

            End Select
        #End If
        

Open in new window

0
 

Author Closing Comment

by:developingprogrammer
ID: 39843904
i took what Pat taught me and modified it for my situation
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now