XLM documentation

K

katie_c

Having never programmed XLM macros, can anyone point me to documentation on how to program them and execute them? Some of my VBA macros have no equivalents in AppleScript and I'm trying to modify them to work within 2008.
 
B

Bob Greenblatt

Having never programmed XLM macros, can anyone point me to documentation on
how to program them and execute them? Some of my VBA macros have no
equivalents in AppleScript and I'm trying to modify them to work within 2008.
Click to expand...
The only documentation I can think of is really old from the Excel 4
vintage. It may be remotely possible to find some of these older books. The
documents referred to in the other posts (Yes Nick, it¹s fine for Mac
versions also) are reference manuals that describe each function.

To write XLM code, think of them as simply excel formulas that get executed
in sequence, one at a time down the column. (There are exceptions, like Goto
and some of the loop structures that change the sequence.) You can define
names to the cells containing the macros, and refer to these in when a macro
is assigned to an object; or, you can use the names to make your macros
clearer. Try Google or Google groups and you may find some stuff and some
examples to get you started.
 
K

katie_c

Thanks for the suggestions.

Just to clarify, do I need to select the whole range of the macro sheet with the XLM macro, and name that, or just the first cell. Also, is there a specific start line that I need for a macro, or just begin with the first function?
 
N

Niek Otten

You just need to name the first cell.

You can have many functions on one sheet.

Here's an example, the bold cells have names like their contents.
I use named arguments; you can use cell references instead.
In Excel 2007 you would need longer variable names to avoid confusion with cell references.
I happened to use R1C1 references, but you can use A1 references if you prefer that (you probably do!)


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


MaxVan2
=ARGUMENT("zv1")
=ARGUMENT("zv2")
=MAX(zv1,zv2)
=RETURN(R[-1]C)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
MinVan2
=ARGUMENT("zv1")
=ARGUMENT("zv2")
=MIN(zv1,zv2)
=RETURN(R[-1]C)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Termijnf
=ARGUMENT("aantprem")
=VLOOKUP(AantPrem,TermijncodeTab,2,FALSE)
=RETURN(R[-1]C)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




| Thanks for the suggestions.
|
| Just to clarify, do I need to select the whole range of the macro sheet with the XLM macro, and name that, or just the first
cell. Also, is there a specific start line that I need for a macro, or just begin with the first function?
 
N

Niek Otten

Of course the Bold effect doesn't show here.
As you may have guessed, the function names are

MaxVan2
MinVan2
Termijnf

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| You just need to name the first cell.
|
| You can have many functions on one sheet.
|
| Here's an example, the bold cells have names like their contents.
| I use named arguments; you can use cell references instead.
| In Excel 2007 you would need longer variable names to avoid confusion with cell references.
| I happened to use R1C1 references, but you can use A1 references if you prefer that (you probably do!)
|
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
| MaxVan2
| =ARGUMENT("zv1")
| =ARGUMENT("zv2")
| =MAX(zv1,zv2)
| =RETURN(R[-1]C)
| +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| MinVan2
| =ARGUMENT("zv1")
| =ARGUMENT("zv2")
| =MIN(zv1,zv2)
| =RETURN(R[-1]C)
| +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
| Termijnf
| =ARGUMENT("aantprem")
| =VLOOKUP(AantPrem,TermijncodeTab,2,FALSE)
| =RETURN(R[-1]C)
| +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|
|
|
|
|| Thanks for the suggestions.
||
|| Just to clarify, do I need to select the whole range of the macro sheet with the XLM macro, and name that, or just the first
| cell. Also, is there a specific start line that I need for a macro, or just begin with the first function?
|
|
 
N

Niek Otten

The cell with the function name in it is optional. You can also just name the first ARGUMENT() function
Any cell not starting with an equals sign is treated as comment

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Of course the Bold effect doesn't show here.
| As you may have guessed, the function names are
|
| MaxVan2
| MinVan2
| Termijnf
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| You just need to name the first cell.
||
|| You can have many functions on one sheet.
||
|| Here's an example, the bold cells have names like their contents.
|| I use named arguments; you can use cell references instead.
|| In Excel 2007 you would need longer variable names to avoid confusion with cell references.
|| I happened to use R1C1 references, but you can use A1 references if you prefer that (you probably do!)
||
||
|| --
|| Kind regards,
||
|| Niek Otten
|| Microsoft MVP - Excel
||
||
|| MaxVan2
|| =ARGUMENT("zv1")
|| =ARGUMENT("zv2")
|| =MAX(zv1,zv2)
|| =RETURN(R[-1]C)
|| +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|| MinVan2
|| =ARGUMENT("zv1")
|| =ARGUMENT("zv2")
|| =MIN(zv1,zv2)
|| =RETURN(R[-1]C)
|| +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|| Termijnf
|| =ARGUMENT("aantprem")
|| =VLOOKUP(AantPrem,TermijncodeTab,2,FALSE)
|| =RETURN(R[-1]C)
|| +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
||
||
||
||
||| Thanks for the suggestions.
|||
||| Just to clarify, do I need to select the whole range of the macro sheet with the XLM macro, and name that, or just the first
|| cell. Also, is there a specific start line that I need for a macro, or just begin with the first function?
||
||
|
|
 
J

Jack Marr

I've created, saved, and run (!) a basic XLM macro, and now would like
to go back and edit it, but can't access the contents. It seems that
the file opened, but I can't get at the Macro1 sheet, or seemingly any
other bits of the macro file although it didn't give me any error
message. What trick is required to edit an existing .xlam file?
 
B

Bob Greenblatt

I've created, saved, and run (!) a basic XLM macro, and now would like
to go back and edit it, but can't access the contents. It seems that
the file opened, but I can't get at the Macro1 sheet, or seemingly any
other bits of the macro file although it didn't give me any error
message. What trick is required to edit an existing .xlam file?
Click to expand...
There shouldn't be any trick. Did you hide the macro sheet? Is the sheet tab
not visible? Try Format sheet unhide.
 
N

Niek Otten

Save it as a macro-enabled workbook (xlsm), not as xlam. In the VBE one could set the IsAddin property to false; I don't know how
to do that in 2008

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I've created, saved, and run (!) a basic XLM macro, and now would like
| to go back and edit it, but can't access the contents. It seems that
| the file opened, but I can't get at the Macro1 sheet, or seemingly any
| other bits of the macro file although it didn't give me any error
| message. What trick is required to edit an existing .xlam file?
|
|
 
J

Jack Marr

There shouldn't be any trick. Did you hide the macro sheet? Is the sheet tab
not visible? Try Format sheet unhide
Click to expand...

I didn't hide it and Format -> Sheet -> Unhide is grayed out.
Window ... shows just the default Workbook1 open after I double-
clicked my foo.xlam file.

Save it as a macro-enabled workbook (xlsm), not as xlam. In the VBE one could set the IsAddin property to false; I don't know how
to do that in 2008
Click to expand...

Hmmm... I've done that but can't add the .xlsm file to the Add-Ins
list via Tools -> Add-Ins. The .xlsm file is grayed out.

Are these actual bugs or just quirks of XLM usage that I don't
understand yet?
 
N

Niek Otten

I can just speak for the Windows versions, but I assume that in this respect it behaves the same.
In an Add-in you can't edit worksheets or macro sheets, they're invisible. You have to make it a non-add-in first or keep a
non-addin version.
I remember I used to keep xla and xls versions in sync, because I might need the xls for editing (and creating a new xla).

But it's all so long ago!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


There shouldn't be any trick. Did you hide the macro sheet? Is the sheet tab
not visible? Try Format sheet unhide
Click to expand...

I didn't hide it and Format -> Sheet -> Unhide is grayed out.
Window ... shows just the default Workbook1 open after I double-
clicked my foo.xlam file.

Save it as a macro-enabled workbook (xlsm), not as xlam. In the VBE one could set the IsAddin property to false; I don't know
how
to do that in 2008
Click to expand...

Hmmm... I've done that but can't add the .xlsm file to the Add-Ins
list via Tools -> Add-Ins. The .xlsm file is grayed out.

Are these actual bugs or just quirks of XLM usage that I don't
understand yet?
 
B

Bob Greenblatt

I can just speak for the Windows versions, but I assume that in this respect
it behaves the same.
In an Add-in you can't edit worksheets or macro sheets, they're invisible. You
have to make it a non-add-in first or keep a
non-addin version.
I remember I used to keep xla and xls versions in sync, because I might need
the xls for editing (and creating a new xla).

But it's all so long ago!
Click to expand...
Yep, that's the way it still works. Maintain 2 versions, the one you can
edit and modify and then save it as an add-in if that's what you want.
 
P

peterthebag

Hi,
Sorry to interrupt but I am also trying on a little XL4M. I'm
currently using ex 2004 on tiger. I was wondering how do I save as a
macro-enabled workbook (xlsm)? Also, how do I save as an addin?

Many thanks,

PtB
 
B

Bob Greenblatt

Hi,
Sorry to interrupt but I am also trying on a little XL4M. I'm
currently using ex 2004 on tiger. I was wondering how do I save as a
macro-enabled workbook (xlsm)? Also, how do I save as an addin?

Many thanks,

PtB
Click to expand...
In Excel 2004, all workbooks are macro enabled. Just include the macro sheet
and save the file as a normal xls. To save it as an add-in, go to file-save
and select add-in. Realize that once the file is an add-in it can not be
edited or made visible. That's why you need 2 copies, the xls file to work
with and the add-in to test with or implement. Also realize that you do not
ever really need to make an add-in, unless you'd like it to function as such
and be invisible. It's probably easier to keep it as an xls. You could add
an auto_open routine to hide it if you don't want to look at it.
 
J

Jack Marr

... Realize that once the file is an add-in it can not be
edited or made visible. That's why you need 2 copies, the xls file to work
with and the add-in to test with or implement. Also realize that you do not
ever really need to make an add-in, unless you'd like it to function as such
and be invisible. It's probably easier to keep it as an xls. You could add
an auto_open routine to hide it if you don't want to look at it.
Click to expand...

Thanks, that's extremely useful. Ultimately I'd want the internals to
be hidden from the end user, but while I'm developing/debugging of
course I need to see the source "early and often". And it sounds like
I need to keep the source copy around, so this is essentially a source-
and-executable relationship, for .xlam's anyway.
 
M

Manuel T.

Hi

I'm trying to learn how to write XL4 Macros as a replacement for VBA.

I can't get Niek's sample to work. I keep getting a message "That function is not valid".

What am i missing? Jack, could i have the file where you constructed the basic macro?

Thanks in advance.
 
S

sketchturner

The "function is not valid" error was because i needed to write the macro in a Macro sheet (insert/sheet/Excel 4 macro).

However, I'm still not able to run the macro. Any suggestions will be appreciated.

Manuel T
 
S

sketchturner

Solved it. I have to mark the names as commands in Insert\Define\Names dialog.

I didn't notice that option before because it appears after the Macro sheet is inserted.

Thanks.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to select and delete levels of a document 2
Word hyperlinks not working 2
question on writing new XLM code 2
XLM macros 1
XLM macro dialog.box command 6
Need Help with Macro Security 1
Excel 4 macro File Functions Add-in 3
Converting URLs in comments to Markdown-formatted links 2

Top