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

Andrew XJ

Board Regular
Joined
Feb 21, 2002
Messages
77
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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi Andrew...
Why can't you use the Excel funtions in your function?
Student?
What is your function attempting to accomplish?
Tom
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.......
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top