Coding Etiquette

[Chad Cameron]

Hi All,

I was running Excel 2000. All my macros always refered to excel files as
myfile.xls. We are switching to 2007 and now all files are *.xlsx or xlsm
or xlsb blah blah blah.

How should I change my coding? Do I hard code in the file name as
myfile.xlsm? What happens if the Excel 2010 uses *.xlsmm or somthing stupid
like that. Then do I recode everything again?

There has to be an easier way.

Thanks
Chad

 

[Chad Cameron]

Sorry here is my line of code in question

Application.Run "'SLOPE_MONITORING_4XX_WightPit.xls'!ThisWorkbook.main"

and I have to change it to

 

[Chad Cameron]

hmm apprently Ctrl + Enter sends the posts. Here is the rest of my prior
post

Application.Run "'SLOPE_MONITORING_4XX_WightPit.xlsm'!ThisWorkbook.main"

 

[Peter T]

If your files are similarly named in 2003- / 2007+, except for extension,
you could do soemthing like this -

dim sMacro as string

sMacro = "'my File.xls" ' note first character apostrophe
if val(application.version) >=12 then
sMacro = sMacro & "m"
end if
sMacro = sMacro & "!myModule.myMacro"

application.run sMacro

In passing, you have "Workbook.Main", in general best to keep code in the
ThisWorkbook module to a minimum, limited to Workbook events only. Suggest
put "main" in a normal module

Regards,
Peter T

 

[Chad Cameron]

Thanks Peter,

If your files are similarly named in 2003- / 2007+, except for extension,
you could do soemthing like this -

dim sMacro as string

sMacro = "'my File.xls" ' note first character apostrophe
if val(application.version) >=12 then
sMacro = sMacro & "m"
end if
sMacro = sMacro & "!myModule.myMacro"

application.run sMacro

In passing, you have "Workbook.Main", in general best to keep code in the
ThisWorkbook module to a minimum, limited to Workbook events only. Suggest
put "main" in a normal module

Regards,
Peter T