Excel 2007: custom worksheet function?

[TomBarsh]

hello.

Is it possible to create "custom" worksheet functions in VBA in Excel 2007?

I used to do this quite easily in Excel 5. I can't find how to do this in the new version.

This MS article doesn't seem to work for the 2007 (running on Vista):

http://office.microsoft.com/en-us/excel/HA010548461033.aspx

 

[electricpete]

From what I hear MS Office 2007 took away the toolbars and made everything hard to find.

There is supposed to be some kind of patch out there that will add the old toolbars back in and make the new program half-way compatible with users of the old program.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Correction: not just the toolbars but the menu's. The patch will put the menu's back.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[TomBarsh]

Okay, I see. The function has to go into a "module". It can't go into the code for a worksheet or the workbook.

 

[Feric]

Hi there:

From the spreadsheet mode, hit ALT+F11 in order to get to the VBA environmnet. Work with modules and create function in order to get custom functions developed.

Thanks,

Gordan

http://engware.i-dentity.com

 

[IDS]

You can find a short tutorial on writing UDFs in Excel here:

http://newtonexcelbach.wordpress.com/2008/02/21/hello-world-in-a-udf/

In XL2007 make sure the developer tab is displayed on the ribbon, or use the old keystroke sequences (Alt-F11 for the VBE)

Doug Jenkins
Interactive Design Services

http://www.interactiveds.com.au

 

[prost]

Excuse my ignorance--there is another way in pre2007 versions of Excel to introduce custom worksheet functions BESIDES writing a function macro:
Function Asquared(a as double)
Asquared=a*a
end function

accessed by typing in cell "=Asquared(a1)" where 'a1'
has the number you want squared.

What is the other way? If there is no other way, can't you do the same thing in Excel 2007?

 

[TomBarsh]

prost,

Before VBA there was the Excel macro language. Custom worksheet functions could be written in the macro language on an XML sheet.

VBA is much simpler (more straightforward) than the XML language. On the other hand, the XML worksheet functions could be assigned to categories other than "user". In fact, you could create your own categories. For example, I had an XML sheet with dozens and dozens of formulas from the AISC steel design standard, these were assigned to the "AISC" function category. Now that is a feature that I miss!

Nowadays, I tend not to use custom worksheet functions due to the nature of what I'm doing. But I had a need for it over the weekend. It was not clear to me that the function had to be in a "Module" rather than in the VBA code associated with a worksheet or the workbook. (Editorial opinion: I couldn't find anything in the documentation in 2007 and there is no apparent difference in these VBA "pages" once you're in the VBA environment, so what's the difference?). But I finally got that figured out.

The XML language is still available as a mostly undocumented feature. I think you can even continue to create new such sheets. Maybe that's a back door to getting those truly custom function categories, like "AISC", "ACI", "ASME", etc.

There are a few XML functions that I continue to use without even thinking about them. I just checked and in one of my major ASME workbooks is an XML macro for interpolation within tables of data, I got this from PC Mag around 1993.

(Note, the 'XML' does not refer to the 'XML' variant of 'HTML'!)

 

[TomBarsh]

okay, that "XML" should indeed be "XLM"!

 

[prost]

TomBarsh--man, I had no idea-r...that there used to be this whole substructure in Excel that allowed you to create almost transparent functions (transparent to the noncreator). I'll bet I've encountered this and didn't even know it, some spreadsheet that had some functions I didn't understand where they came from (if you can't find the code, you can't debug it! naturally..)