Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

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

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards
    XJ

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...
    Kind Regards,
    Ivan F Moala From the City of Sails

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?



    Regards
    XJ

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards
    XJ

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone enlighten me that?

    Thx
    Regards
    XJ

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Singapore
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.......
    Regards
    XJ

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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,

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •