×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel 2007: custom worksheet function?

Excel 2007: custom worksheet function?

Excel 2007: custom worksheet function?

(OP)
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  

RE: Excel 2007: custom worksheet function?

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.

RE: Excel 2007: custom worksheet function?

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.

RE: Excel 2007: custom worksheet function?

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

RE: Excel 2007: custom worksheet function?

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

RE: Excel 2007: custom worksheet function?

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?

RE: Excel 2007: custom worksheet function?

(OP)
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'!)

RE: Excel 2007: custom worksheet function?

(OP)
okay, that "XML" should indeed be "XLM"!  

RE: Excel 2007: custom worksheet function?

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..)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources