Converting VBA macros to XML with Excel 2007?

[Halibut]

I haven't bought Office 2008 yet. But my decision would be speeded if it were possible to open spreadsheets with macros or functions done in Excel 2004 and save those macros/functions to XML in Office 2007 format. Thye could then be brought back into Office 2008 where they should work. Is that possible? Because I really can't stand the idea of converting some really complicated functions by had. From what I have read on the MS webpages, you can save VBA off into XML.

The real problem is finding someone who has Excel 2007 installed. In the corporate world where I work, no one has it.

 

[JE McGimpsey]

I haven't bought Office 2008 yet. But my decision would be speeded if it were
possible to open spreadsheets with macros or functions done in Excel 2004 and
save those macros/functions to XML in Office 2007 format. Thye could then be
brought back into Office 2008 where they should work. Is that possible?
Because I really can't stand the idea of converting some really complicated
functions by had. From what I have read on the MS webpages, you can save VBA
off into XML.<br>
<br>
The real problem is finding someone who has Excel 2007 installed. In the
corporate world where I work, no one has it.

Couple of clarifications that probably need to be made...

1) Office 2007 and 2008 both use a new XML *file format* (XML for
"Extended Markup Language"). Office 2008 can read and write the XML file
formats, including those which contain VBA macros (e.g., .xlsm), but
doesn't execute VBA.

2) All versions of Office from XL4 -> X12 support *XLM* macros - XLM for
"eXceL Macro language". Note the different order of the letters. These
macros exist on macro sheets in workbooks.

3) As far as I know, there are no tools available to automatically
convert VBA to XLM. In many cases, macros can be converted by hand
(assuming you know both VBA and XLM).

4) Since you can write XLM macros in ALL versions of XL, there's no need
to choose XL2007. The macros can be written in XL2004 or XL2008 just
fine.

 

[Tobias Weber]

possible to open spreadsheets with macros or functions done in Excel 2004 and
save those macros/functions to XML in Office 2007 format. Thye could then be
brought back into Office 2008 where they should work. Is that possible?

I suppose this is the kind of question we wouldn't see without the web
interface...

Wether your files are in .xls (aka 2004/old) or .xlsx (aka 2007/XML)
format has nothing to do with macros/scripts.

Those can be written in VBA (Office:mac *up to* 2004, Windows Office
including 2007) or AppleScript (Office:mac only). In contrast to file
formats a conversion between those two is not possible.

There is no way to get workbooks which rely on VBA to work in 2008
without rewriting everything.

 

[Tobias Weber]

2) All versions of Office from XL4 -> X12 support *XLM* macros - XLM for
"eXceL Macro language". Note the different order of the letters. These
macros exist on macro sheets in workbooks.

I have never heard of that

In general: trust the MVPS' answers over others

 

[Halibut]

Ah. Yes. There is this problem with conversion, and yes I know it won't be simple. But I'll leave this to ponder from the MS Office 2008 page on what what to do if your VBA macros don't run:

" Solution: Save the macro in another macro-enabled file format.
Macros are preserved when you save a file in any of the macro-enabled file formats, even if you've made changes to the contents of the file.
In the This file contains Visual Basic macros alert that appears in Office 2008 when you open a file that contains a Visual Basic macro, click Open.
On the File menu, click Save As.
On the Format pop-up menu, select one of the following macro-enabled file formats.
Excel Macro‑Enabled Workbook (.xlsm)
Excel Macro‑Enabled Template (.xltm)
Excel Binary Workbook (.xlsb)
Excel Add‑In (.xlam)
Excel 97‑2004 Workbook (.xls)
Excel 97‑2004 Template (.xlt)
Excel 97‑2004 Add-In (.xla)"

Maybe they are great programmers but have a bit of trouble with the English language.

Thanks for all the comments.

 

[Bob Greenblatt]

Ah. Yes. There is this problem with conversion, and yes I know it won't be
simple. But I'll leave this to ponder from the MS Office 2008 page on what
what to do if your VBA macros don't run:

" Solution: Save the macro in another macro-enabled file format.
Macros are preserved when you save a file in any of the macro-enabled file
formats, even if you've made changes to the contents of the file.
In the This file contains Visual Basic macros alert that appears in Office
2008 when you open a file that contains a Visual Basic macro, click Open.
On the File menu, click Save As.
On the Format pop-up menu, select one of the following macro-enabled file
formats.
Excel Macro‑Enabled Workbook (.xlsm)
Excel Macro‑Enabled Template (.xltm)
Excel Binary Workbook (.xlsb)
Excel Add‑In (.xlam)
Excel 97‑2004 Workbook (.xls)
Excel 97‑2004 Template (.xlt)
Excel 97‑2004 Add-In (.xla)"

Maybe they are great programmers but have a bit of trouble with the English
language.

Thanks for all the comments.

We’re not too bad with the English Language. The problem is correctly
interpreting ambiguous posts. Upon rereading your original posts, I can
offer:
- VBA macros will not work in Excel 2008.
- Excel 2008 WILL open workbooks containing VBA macros. It will completely
ignore them, AND save the macros untouched when the workbook content is
altered and saved IF you ask it to do this when the file is opened.
- There is NO automatic conversion from VBA to XLM Macros
- Old XLM macros WILL work, and new XLM macros can be written, modified and
used in Excel 2008
- Your expectation that VBA macros can be converted by opening the workbooks
in Excel 2007 and saving them in the new XML file formats and then reading
them into Excel 2008 is wrong. The workbooks should be fine, but the macros
will not be converted and will not work in Excel 2008.

 

[Halibut]

Thanks Bob. I has passed the bit I quoted above to a programmer friend of mine and we came up with both interpretations. Either it would or it wouldn't. I bet on it wouldn't. Enough said. Subject is dead.

Bill

 

[Byron Allred]

How much trouble is it to re-write VBA macros to XML macros in Excel 2008? I don't know a great deal about Excel, so I'm wondering what the learning curve would be like. I have a spreadsheet with a bunch of VBA macros that I would need to be converted in order to use Excel 2008.

 

[Bob Greenblatt]

How much trouble is it to re-write VBA macros to XML macros in Excel 2008? I
don't know a great deal about Excel, so I'm wondering what the learning curve
would be like. I have a spreadsheet with a bunch of VBA macros that I would
need to be converted in order to use Excel 2008.

The learning curve is very high. The XLM "language" is not really a
programming language, but an extension of Excel formulas. My educated guess
is that you can convert (code, test, and verify) about 10-15 lines of VBA
code per hour assuming you are thoroughly familiar with both VBA and XLM.
(This is the basic metric I would use in establishing a quote for
conversion.)