Functions within functions

The UK's most regular instructor-led training courses.
Training information: excel 2003 visual basic intermediate course · Excel courses london · Microsoft Excel Training
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version Standard

James has attended:
Excel Advanced course
Excel VBA course
Excel VBA Advanced course

by - delegate James [4 posts] (2008 Mar 18 Tue, 16:05) Reply

How do you call a predefined function in a User defined function?

Version	Date	Location	Places available	Book	Next place rate:
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2009 Jan 8 Thu	Bloomsbury	2	book now	£215	£215
2007	2009 Jan 8 Thu	Bloomsbury	3	book now	£224	£224
2007	2009 Jan 12 Mon	Bayswater	4	book now	£224	£224
2007	2009 Jan 13 Tue	Bloomsbury	2	book now	£224	£224
2002/XP	2009 Jan 13 Tue	Tooting	0	FULL
2003	2009 Jan 15 Thu	Southwark	1	book now	£215	£215
Full Schedule: See all 136 Excel Intermediate course dates. Bookings currently available until 30th March 2010.

RE: Functions within functions

by - trainer Stephen gold contributer [394 posts] (2008 Mar 28 Fri, 09:58) Edited on 2008 Mar 28 Fri, 09:59 Reply

Hi James

Thank you for your question

I am assuming that you want to use one of the standard functions within your user defined function

To do this use need to use the work sheet function property of the application object.

For example the following code calculates the average of a range of cells and then returns it to a variable called MyAverage

MyAverage = Application.WorksheetFunction.Average("B1:D15")

Hope this is useful

Regards

Stephen

· Excel in the Home Office
· Microsoft Excel Training: Not Just for Newbies Anymore
· Tap the Power of Recorded Macros with MS Excel Courses
· Microsoft Excel Courses Teach Advanced Macro Techniques
· Do You Really Need Excel VBA Training?

Please browse our web site to find out more about
excel 2003 visual basic intermediate course and other Microsoft training courses.

Excel tip:

Sum Up All the Values in A Column

If you want to quickly calculate the Summed values of all cells in a column in Excel 2003 normally you would use the SUM formula. (eg if you wanted to calculate the values in Column C rows 10 to 25) the formula would be:

=SUM(C10:C25)

However, if you keep adding values to column C you would keep having to modify the above SUM formula which can get quite annoying.

To get around this you can sum all the values in a column using the following formula:

=SUM(COLUMN:COLUMN)

Which, in our example, would be:

=SUM(C:C)

NOTE You cannot place this formula in column C, or else Excel 2003 will show a circular reference error.

The formula must be placed in any other column, EXCEPT the one being calculated.