Custom cell formula

  • Thread starter Thread starter Markus Wildgruber
  • Start date Start date
M

Markus Wildgruber

Hi!

We make software for financial accounting and want to integrate some of that
functionality in MS Excel.
Is it possible to add custom formulas that can be used in cell expressions?

As the user now is able to use the AVERAGE-function he should be able to use
our custom formulas e.g. GET_OUR_VALUE( 'NET PROFITS' ):
=AVERAGE(A1:A4)
=GET_OUR_VALUE( 'NET PROFITS' )

How can we achieve this?

TIA,

Markus
 
Hi Markus
one way: you can use User Defined Functions (UDFs) for this. In VBA
create a macro:
Public Function GET_OUR_VALUE (net_profits as double) as double
' your code...
End Function

Frank
 
Markus,

Excel give you the ability to add User Defined Functions (UDF). This is VBA
code that allows you to build custom functions.

These functions act similarly to Excel functions, in that they can take
arguments, and they return a value to the calling cell (note that the amount
of manipulation of cell attributes is severely limited, almost nil, so you
cannot set a cell's colour in a UDF for example). So this is not a valid UDF

Function myUDF()
ActiveCell.Interior.colrindex = 3
End Function

As I said, you can pass arguments ( 1 or many). This example passes a cell
value and doubles it

Function myUDF(cell As Range)
myUDF = 2 * cell.Value
End Function

called like so, =myUDF(E1), and as with normal Excel functions, it will
recalculate if the value in E1 is changed.

You can also have error handling as Excel functions do. As an example, you
may want to check that only one cell is passed as the argument, and error if
more than 1.

Function myUDF(cell As Range)
If cell.Count > 1 Then
cell = CVErr(xlErrValue)
Else
myUDF = 2 * cell.Value
End If
End Function

A call like this =myUDF(E1:E2) will produce a #VALUE in the cell.

These functions appear in the Function Wizard as well, under the 'User
Defined' category. It is possible to create custom categories, but that's
another story. The most difficult part is adding explanations for the
arguments as for built-in functions, but you probably don't need that
anyway.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello Markus,

Both Bob and Frank gave you some helpful information. I am adding a link to
Microsoft Article titled: "Create your own worksheet functions". The link
is listed below:

http://office.microsoft.com/assistance/preview.aspx?AssetID=HA010548461033&C
TT=1&Origin=EC010229831033&QueryID=Rzr4zXJSI

I hope this helps!

Sonny Kocak
(e-mail address removed)
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Markus Wildgruber" <[email protected]>
| Subject: Custom cell formula
| Date: Wed, 4 Feb 2004 09:55:07 +0100
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#[email protected]>
| Newsgroups: microsoft.public.excel.programming
| NNTP-Posting-Host: mx01.syska.de 212.86.203.50
| Path:
cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.
phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.excel.programming:459148
| X-Tomcat-NG: microsoft.public.excel.programming
|
| Hi!
|
| We make software for financial accounting and want to integrate some of
that
| functionality in MS Excel.
| Is it possible to add custom formulas that can be used in cell
expressions?
|
| As the user now is able to use the AVERAGE-function he should be able to
use
| our custom formulas e.g. GET_OUR_VALUE( 'NET PROFITS' ):
| =AVERAGE(A1:A4)
| =GET_OUR_VALUE( 'NET PROFITS' )
|
| How can we achieve this?
|
| TIA,
|
| Markus
|
|
|
 
Hi!

Thanks for your answers. As a ISV it is very important to us to be able to
deploy the office solution easily. Do we have to use an excel worksheet
template (*.xlt) with the macros included or are there other ways that allow
us to include new macro functions in a user's excel application?

How can we change the category of the functions and present help to the user
in a way without using additional Dlls (that make deployment more complex)?

TIA again,

Markus
 
Markus,

A template is one way, but the big disadvantage for this method is that
every workbook created from the template contains those macros.

A better way is to create an addin. That addin has to be distributed, but
only once (unless you update it), but once installed, it is constantly
available, and it is a one-time solution.

As for function categories, Laurent Longre's provides a solution at
http://longre.free.fr/english/func_cats.htm

Here is an MS article, but Laurent (in the above article) states that the
approach documented here is unreliable:

http://support.microsoft.com/support/kb/articles/q137/5/26.asp
XL: How to Add a New Category to Function Category List


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Markus,

This first link point to an article that covers a gambit is automation
issues and sample with Office (varies languages and versions). Please, the
"Office Add-ins and Components" section of this document as alternate
approach to use XL Templates (*.xlt's).

Name: Microsoft Office Development with Visual Studio
Link:
http://msdn.microsoft.com/library/default.asp?URL=/library/techart/VSOfficeD
ev.htm

The Links list below will assist you in your Category.

Name: How to Add a New Category to Function Category List
Link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;137526

Name: Custom Function in the Wrong Category in Function Wizard
Link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;141825

Name: XL97: Options Not Available in Macro Options Dialog Box
Link: http://support.microsoft.com/default.aspx?scid=kb;EN-US;157108


Regards,

Sonny Kocak
(e-mail address removed)
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top