ExcelBanter - Custom cell formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Custom cell formula (https://www.excelbanter.com/excel-programming/290188-custom-cell-formula.html)

Custom cell formula

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

Custom cell formula

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 Wildgruber wrote:
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

Custom cell formula

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)

"Markus Wildgruber" wrote in message
...
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

Custom cell formula

Hi
in addition to Bob. If you want to create custom categories as well as
explanations for your UDFs you may have a look at the free add-in
FUNCUSTOMIZE.DLL (http://longre.free.fr/english/)

HTH
Frank

Bob Phillips wrote:
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.

"Markus Wildgruber" wrote in message
...
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

Custom cell formula

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

"Bob Phillips" schrieb im Newsbeitrag
...
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)

"Markus Wildgruber" wrote in message
...
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

Custom cell formula

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.../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 Wildgruber" wrote in message
...
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

"Bob Phillips" schrieb im Newsbeitrag
...
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)

"Markus Wildgruber" wrote in message
...
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

Custom cell formula

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/de...hart/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...b;EN-US;137526

Name: Custom Function in the Wrong Category in Function Wizard
Link: http://support.microsoft.com/default...b;EN-US;141825

Name: XL97: Options Not Available in Macro Options Dialog Box
Link: http://support.microsoft.com/default...b;EN-US;157108

Regards,

Sonny Kocak

Microsoft Developer Support

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