how to see the Excel Embedded function? (How to write UDF)

[Andrew XJ]

I need to write some user defined fuctions and these fuctions will be used by Excel cells in spreadsheets.
Now the problem is that i can't use Excel Embeded fuctions such as "Find", "Select", etc. Could you help me with this problem?
In fact i also want to know how the Excel embeded functions are defined.

regards
Andrew XJ

_________________
Regards
XJ

[ This Message was edited by: Andrew XJ on 2002-04-28 22:18 ]

[Tom Schreiner]

Hi Andrew...
Why can't you use the Excel funtions in your function?
Student?
What is your function attempting to accomplish?
Tom

[Andrew XJ]

you can assign formulas to Cells, rite? What i need to do is writing my own formulas. But the problem is that my own formula can only assign return value. I can't call Excel embedded functions in my function.

[Ivan F Moala]

On 2002-05-02 05:19, Andrew XJ wrote:
you can assign formulas to Cells, rite? What i need to do is writing my own formulas. But the problem is that my own formula can only assign return value. I can't call Excel embedded functions in my function.

What exactly are you trying to do with your
UDF...?? perhaps we can help...

[Andrew XJ]

it's a general question.
e.g, i need to write a formula named by RECIPROCAL(). I can create a module and a function:
Function RECIPROCAL(Byval input1 As Double) As Double
RECIPROCAL = 1/input1
End Function

This simple function of cos will cause no problem. But what if i need to add some action in the function or call some Excel Embedded function.

Function RECIPROCAL(Byval input1 As Double) As Double
RECIPROCAL = 1/input1
Range("A1") = "test"
'or Sheets("Sheet1").select
'or Range("A1:H99").Find("testing").Select
End Function

Then the function RECIPROCAL can't work properly.
So how to solve this problem?

[Andrew XJ]

In fact, most embedded formulas needs to call other functions or formulas. How can these formulas can do that but mine own can not? Could anyone tell me how to see the codes of some embedded formulas such as SUMIF.

[Andrew XJ]

Can anyone enlighten me that?

Thx

[Juan Pablo González]

UDF's can't select, change, modify, or whatever you can think of, the environment of the spreadsheet. They can only return a value.

Why SUMIF works with other sheets ? simply, because it doesn't select anything. Actually, you don't need (99% of the time) to select anything in your macros for them to work properly.

[Andrew XJ]

Juan Pablo, i agree with you that select can be avoided in most cases. But how about function Find? This function is very useful in UDF. Then do you mean that the only way is to implement the find function using UDF by for loop?? this is the only way i can think of.......

[Jack in the UK]

If it any help i keep UDF as simple as poss like Juan says they will only return a value, ok you throw them in them in the mixer and carry out commands / action but to me UDF is USer Defined function so will make some math run on a cell and return some result EXACTLY as you want.

so i would stick to that and make a command button or target code make the changes on a different script

some ideas,