User Defined Functions in Excel 2008

kgarnjost · Jan 23, 2008

Can anyone tell me if and how one creates user defined functions in Excel 2008. I have several VBA user defined functions I use in Excel 2004, but I have not been able to find anything about how to convert them for use in Excel 2008. Is it possible?

Phillip Jones · Jan 24, 2008

VBA and Macros That depend upon VBA are dead in the water.
There is no VBA in 2008 for now. There is some esoteric talk of
something called VBA.net That does work On Mac. VBA support for Mac has
always been one notch above not working on Mac and when Apple went to
Intel for chips That broke the camel's back so far as VBA was concerned.

They didn't really have to get out support before roll out because it
would have set back the project another 2 to 4 years or more. And the
Vast majority of Mac users rarely if ever used it.

I know from my own experience I've had macros turned off in MS products
since the days of Word 95 due to virus concerns. And truth be told
unless OSX actually uses any apple scripts behind the scenes, I don't
use any scripting language period.

I haven't even learned anything about automators. I just use the
applications the way they are, and If I can't get something done without
the need of some outside script I just don't do it.

But that's me.

Can anyone tell me if and how one creates user defined functions in
Excel 2008. I have several VBA user defined functions I use in Excel
2004, but I have not been able to find anything about how to convert
them for use in Excel 2008. Is it possible?

--
------------------------------------------------------------------------
Phillip M. Jones, CET |LIFE MEMBER: VPEA ETA-I, NESDA, ISCET, Sterling
616 Liberty Street |Who's Who. PHONE:276-632-5045, FAX:276-632-0868
Martinsville Va 24112 |[email protected], ICQ11269732, AIM pjonescet
------------------------------------------------------------------------

If it's "fixed", don't "break it"!

mailto

[email protected]

<http://www.kimbanet.com/~pjones/default.htm>
<http://www.kimbanet.com/~pjones/90th_Birthday/index.htm>
<http://www.kimbanet.com/~pjones/Fulcher/default.html>
<http://www.kimbanet.com/~pjones/Harris/default.htm>
<http://www.kimbanet.com/~pjones/Jones/default.htm>

<http://www.vpea.org>

Jim Gordon MVP · Jan 24, 2008

Hi,

You can create them using AppleScript. Any existing functions would have to
be converted to AppleScript in order to work in Excel 2008.

Here¹s a transition guide written by Paul Berkowitz:
http://www.mactech.com/vba-transition-guide/

If you find this approach to be less than satisfactory, please let Microsoft
know by sending them a note. Here¹s the URL
http://www.microsoft.com/mac/suggestions.mspx?product=excel

-Jim

in said:
Can anyone tell me if and how one creates user defined functions in Excel
2008. I have several VBA user defined functions I use in Excel 2004, but I
have not been able to find anything about how to convert them for use in Excel
2008. Is it possible?

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

JE McGimpsey · Jan 24, 2008

Can anyone tell me if and how one creates user defined functions in Excel
2008. I have several VBA user defined functions I use in Excel 2004, but I
have not been able to find anything about how to convert them for use in
Excel 2008. Is it possible?

In addition to Jim's suggestion, you can also code UDFs in XL4 Macro
Language on Macro Sheets.

cwhaley · Jan 24, 2008

In addition to Jim's suggestion, you can also code UDFs in XL4 Macro
Language on Macro Sheets.

Since I don't have Excel 2008 yet, that was going to be my next
question. I never really got into using VBA for macros, but I have
lots of functions and macros that are written in the old Excel 4 macro
language.

So, are you really saying that they will continue to work AND that I
can even develop new XL4 macros in Excel 2008?

It's an ugly language, but it gets the job done.

I was sure they'd eventually phase it out, but if you're right, I'm
glad they haven't. I don't want to try the Applescript alternative
just yet.

kgarnjost · Jan 24, 2008

I looked at the Paul Berkowitz transition guide and could find nothing about how I would code a function with Apple script. The functions I have are like the one pasted below and work just like Excel functions in when entered in a cell. Has anyone managed to create something like this in Apple script and, if so, how?

Function OvrStrt(objTimeCell As Object)
    Dim intRowNum As Integer
    Dim intColNum As Integer
    Dim objDateCell3 As Object
    Dim objDateCell4 As Object
    Dim dtStrtTime As Date
    Dim dtOvrTime As Date
    Dim dtEndTime As Date
    Dim intCount As Integer
    Dim i As Integer

intRowNum = objTimeCell.Row
    intColNum = objTimeCell.Column
    intCount = 0
    Set objDateCell3 = objTimeCell.Offset(0, -6)

If IsEmpty(objDateCell3) Then

Set objDateCell4 = objDateCell3
        Do While IsEmpty(objDateCell4)
            intCount = intCount + 1
            Set objDateCell4 = objDateCell3.Offset(0 - intCount, 0)
        Loop

Else

intCount = 0

End If

dtOvrTime = objTimeCell.Value
    dtEndTime = objTimeCell.Offset(0, -2)
    dtStrtTime = dtEndTime - dtOvrTime

For i = 0 To intCount
        If objTimeCell.Offset(0 - i, -3).Value > dtStrtTime Then
            dtStrtTime = dtStrtTime - (objTimeCell.Offset(0 - i, -3).Value _
                - objTimeCell.Offset(0 - i, -4).Value)
        Else
            Exit For
        End If
    Next

OvrStrt = dtStrtTime

End Function

Bob Greenblatt · Jan 24, 2008

Since I don't have Excel 2008 yet, that was going to be my next
question. I never really got into using VBA for macros, but I have
lots of functions and macros that are written in the old Excel 4 macro
language.

So, are you really saying that they will continue to work AND that I
can even develop new XL4 macros in Excel 2008?

It's an ugly language, but it gets the job done.

I was sure they'd eventually phase it out, but if you're right, I'm
glad they haven't. I don't want to try the Applescript alternative
just yet.

Yes, they will continue to work, AND you can develop new ones in Excel 2008.
However, there is no macro recorder, but you can hand code them. I can't say
for certainty that ALL you old XLM macros will work without change, since I
haven't seen or tried them. But give it a try, they probably will.

Bob Greenblatt · Jan 24, 2008

I looked at the Paul Berkowitz transition guide and could find nothing about
how I would code a function with Apple script. The functions I have are like
the one pasted below and work just like Excel functions in when entered in a
cell. Has anyone managed to create something like this in Apple script and,
if so, how?

Function OvrStrt(objTimeCell As Object)
Dim intRowNum As Integer
Dim intColNum As Integer
Dim objDateCell3 As Object
Dim objDateCell4 As Object
Dim dtStrtTime As Date
Dim dtOvrTime As Date
Dim dtEndTime As Date
Dim intCount As Integer
Dim i As Integer

intRowNum = objTimeCell.Row
intColNum = objTimeCell.Column
intCount = 0
Set objDateCell3 = objTimeCell.Offset(0, -6)

If IsEmpty(objDateCell3) Then

Set objDateCell4 = objDateCell3
Do While IsEmpty(objDateCell4)
intCount = intCount + 1
Set objDateCell4 = objDateCell3.Offset(0 - intCount, 0)
Loop

Else

intCount = 0

End If

dtOvrTime = objTimeCell.Value
dtEndTime = objTimeCell.Offset(0, -2)
dtStrtTime = dtEndTime - dtOvrTime

For i = 0 To intCount
If objTimeCell.Offset(0 - i, -3).Value > dtStrtTime Then
dtStrtTime = dtStrtTime - (objTimeCell.Offset(0 - i, -3).Value _
- objTimeCell.Offset(0 - i, -4).Value)
Else
Exit For
End If
Next

OvrStrt = dtStrtTime

End Function

You could compile the script. And then use a XLM function to call the
script.

Jim Gordon MVP · Jan 25, 2008

Since I don't have Excel 2008 yet, that was going to be my next
question. I never really got into using VBA for macros, but I have
lots of functions and macros that are written in the old Excel 4 macro
language.

So, are you really saying that they will continue to work AND that I
can even develop new XL4 macros in Excel 2008?

It's an ugly language, but it gets the job done.

I was sure they'd eventually phase it out, but if you're right, I'm
glad they haven't. I don't want to try the Applescript alternative
just yet.

Hi

Microsoft has made the Excel 4.0 Macro Reference available online for free
download as an archive containing PDF documents. Here's the URL
http://www.microsoft.com/downloads/details.aspx?FamilyID=00d31943-3ad1-4df1-
9f93-c19c7e84f01c&DisplayLang=en

-Jim

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

Carl Witthoft · Jan 25, 2008

Jim Gordon MVP said:
Microsoft has made the Excel 4.0 Macro Reference available online for free
download as an archive containing PDF documents. Here's the URL
http://www.microsoft.com/downloads/details.aspx?FamilyID=00d31943-3ad1-4df1-
9f93-c19c7e84f01c&DisplayLang=en

-Jim

Thanks for the link. I kind of (well, sort of

) liked programming
in that macro lang over VBA in the first place.

Carl

Jim Gordon MVP · Jan 26, 2008

Hi Carl,

I got to Excel when VBA was being touted as the way to go and never got into
the XLM macros.

Since you have some expertise in them, if you see questions regarding VBA
that could be solved using XLM please jump right in and offer your thoughts
about it.

For example, I just tried (unsuccessfully) to help someone display a
built-in dialog box. Can this be done with an XLM macro?

-Jim

Thanks for the link. I kind of (well, sort of ) liked programming
in that macro lang over VBA in the first place.

Carl

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

Carl Witthoft · Jan 28, 2008

Jim Gordon MVP said:
Hi Carl,

I got to Excel when VBA was being touted as the way to go and never got into
the XLM macros.

Since you have some expertise in them, if you see questions regarding VBA
that could be solved using XLM please jump right in and offer your thoughts
about it.

For example, I just tried (unsuccessfully) to help someone display a
built-in dialog box. Can this be done with an XLM macro?

-Jim

I have to admit it's been a few years since I worked w/ XLM. Anyway, if
you want to call up a dialog box that's part of Excel, I don't think
I've ever done that. Creating your own dialog box is not too difficult
and is well documented IIRC. In the long run, does it matter? Which
is to say, if you can rebuild a dialog box w/ the same actions as a
builtin dialog, why not just do that?

I'll go back and see if I can resurrect any of my old stuff to see what
other "tricks" I may have come up with.

Carl

Bob Greenblatt · Jan 28, 2008

Hi Carl,

I got to Excel when VBA was being touted as the way to go and never got into
the XLM macros.

Since you have some expertise in them, if you see questions regarding VBA
that could be solved using XLM please jump right in and offer your thoughts
about it.

For example, I just tried (unsuccessfully) to help someone display a
built-in dialog box. Can this be done with an XLM macro?

-Jim

Hi Jim,

I've been using XLM since Multiplan, and have developed many stand alone
applications with thousands of lines of XLM code. Yes, you can display that
dialog, it is =patterns?(). However, in the context of a chart fill it does
not seem to apply, as in Excel 2008 it just opens up the choices in the
palette.

And, by the way (sorry for confusing the thread, but..) the crosshatch
patterns are apparently not available for chart items. (At least they don't
seem to work, maybe XLM is broken here.)

Bill W · Jan 28, 2008

Hi Jim,

Bob,

This is interesting. I joined my company in 1996, recoded all of the
XLM macros to VBA, made substantial improvements over the years, and
now I may have to go fishing around the archives to see if I still
have a copy of the old macros. Progress!

On a more serious note, I have made our VBA macros much more robust
than our old XLM macros, which were mostly batch stuff. In your
opinion, should I consider trying if I really want:

1) Decent conditional looping (If/then, do/while, etc)

2) Any chance XLM allows for custom user-forms (aka dialog boxes)

3) I know this is a crazy question considering my options going
forward, but I have a reason for asking ... do XLM macros call
AppleScript at all?

Bob Greenblatt · Jan 29, 2008

Bob,

This is interesting. I joined my company in 1996, recoded all of the
XLM macros to VBA, made substantial improvements over the years, and
now I may have to go fishing around the archives to see if I still
have a copy of the old macros. Progress!

On a more serious note, I have made our VBA macros much more robust
than our old XLM macros, which were mostly batch stuff. In your
opinion, should I consider trying if I really want:

1) Decent conditional looping (If/then, do/while, etc)

Both If, Else.If, Then Else, and loops using While() are part of the XLM
syntax.

2) Any chance XLM allows for custom user-forms (aka dialog boxes)

Yup! Sure does.

3) I know this is a crazy question considering my options going
forward, but I have a reason for asking ... do XLM macros call
AppleScript at all?

Yes, they can call compiled scripts.

MO.Breault · Feb 1, 2008

Both If, Else.If, Then Else, and loops using While() are part of the XLM
syntax.

Yup! Sure does.

Yes, they can call compiled scripts.

How would xlm macros call an Applescript?

Bob Greenblatt · Feb 1, 2008

How would xlm macros call an Applescript?

Try =exec(..)

MO.Breault · Feb 2, 2008

Try =exec(..)

Thank you Bob! That works. Now to get the Applescript to work.

Mike

How to create portable Excel applications?	2	Jan 27, 2018
Shortcuts in OneDrive	0	Dec 29, 2022
How to implement two shifts per day with multiple teams	0	Mar 23, 2022
excel spreadsheet functions written in vba	1	Jan 24, 2012
Export BPMN Visio Chart to Excel	0	Oct 4, 2023
LISTNUM + Mail Merge + INCLUDETEXT : Referencing Paragraph # Can't get to work	0	Mar 6, 2023
Custom Chart Templates Gone??	1	Mar 17, 2010
User Defined Function for Office 2008	2	May 19, 2010

User Defined Functions in Excel 2008

kgarnjost

Phillip Jones

Jim Gordon MVP

JE McGimpsey

cwhaley

kgarnjost

Bob Greenblatt

Bob Greenblatt

Jim Gordon MVP

Carl Witthoft

Jim Gordon MVP

Carl Witthoft

Bob Greenblatt

Bill W

Bob Greenblatt

MO.Breault

Bob Greenblatt

MO.Breault

Ask a Question

Similar Threads