File Name of Excel file in VBA

Question

In Excel 2003 Files are saved with extension .xls and in Excel 2007 .xlsm (macro enabled) Then how we can make our excel file with VBA code run on both version Excel 2003 and Excel 2007? is it good idea to use excel 2003 for programming because those files with VBA code work on excel 2007? I do not know if they are working. I am new to VBA programming.

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-02-27T03:43:08

is it good idea to use excel 2003 for programming because those files with VBA code work on excel 2007?

Abhi

It actually depends on what exactly you plan the application to do. There are many things that you can do in Excel 2007 which you cannot in Excel 2003. So how will you code that in Excel 2003. If you want to do basic stuff then yes, you can code in Excel 2003 so that it works with Excel 2003/2007/2010.

Also it is but natural that if you are coding in Excel 2003 then the file will be saved with an xls extension. If you plan to code in Excel 2007/2010 then save the file as .xls so that it works with Excel 2003.

FOLLOWUP

This is a classic example of what I mean. When you want to sort the data in Excel 2007 and if you record the macro this is what you get but this will not work in 2003.

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:C").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A9"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C9")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

However if you record the macro in Excel 2003, you will get something like this (doing this from memory as I will otherwise have to reboot my pc to record the macro LOL). Now this piece of code will work with every version :)

Sub Macro1()
'
' Macro1 Macro
'

'
    Columns("A:C").Select
    Selection.Sort Key1:=ws1.Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
End Sub

HTH

Sid

File Name of Excel file in VBA

1 Answers