Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: what's this procedure called ?

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Evening all,

    What's this procedure called ?

    activate B1
    insert
    names
    define
    refers to : =a1*1.25
    name it "addtax"
    add it

    I know it's not naming a range and I don't think it's a UDF, don't they need VBA ?, what are these called ?

    ta
    Chris

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

    Default

    Hi Chris...

    Funny feel like we just spoke... remind me to email you a newsletter on UDF i have its the dogs business, A pal Dave Hawley did it, lift from his site or ill mail tommorow if you mail me you know ill forget else wise.

    you are treading on the gurus world of formatting cells in UDF tyson stylie! and so UDF it is.. read the newsletter and you be a guru for sure.

    PS UDF are not all VBA.. As i have told you verbally before VBA id mega cool but not always needed, Aladin and Dave will tell anyone this as will the top gurus in the Excel field.. VBA UDF are SLOOOOOOW!

    In front end are fast.... but VBA willallow the really complex stuff like convertions functions cant do.. rememebr ALWAYS functions return a answer not take otr make actions... make sence! I do hope i do make some sence.

    BTW for them wanting to track the work i talk of so hightly.. read it and rea reda and stick with..

    see archive news letters cant recall the edition, see:

    http://www.ozgrid.com.

    Cheers Dave....

    Free Excel based Web Toolbar available here.

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

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

    Default

    Jack, one quick word.

    A good designed UDF can kick *** on a lot of formulae... for example, try to do the FuzzyMatch challenge with formulas... it's doable, I agree, but, it will take A LOT of Excel formulas, and just one UDF !

    So, they're not the Devil !!!
    Regards,

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

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

    Default

    Im preplexed ......

    I agree UDF is cool and fast buty not in VBA where that can be slooow! IN open play yes they are super cool, is that not what i said in my post?

    I advise against UDF or VBA Custom function just as they can be slow and bloat sheet size, i have not said no good or dont use them, its my opionion... not that i am correct, just my way.

    Im not arguing or disagreeing with you just dont agree, you your way me mine is that not the wonderful thing of this site diofferent opinions and ways to work?

    Read my post i feel i do really agree with you comments bar why VBA if front end excel will do in UDF???

    We may not agree but we will not disagree either.


    Free Excel based Web Toolbar available here.

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

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 11:19, Chris Davison wrote:
    Evening all,

    What's this procedure called ?

    activate B1
    insert
    names
    define
    refers to : =a1*1.25
    name it "addtax"
    add it

    I know it's not naming a range and I don't think it's a UDF, don't they need VBA ?, what are these called ?

    ta
    Chris
    Chris: The result is called a named formula. However, the mechanism that you use is the same as when you create a range name (dynamic or not). It's definitely not a UDF which are always written in VBA. But a UDF can also be written in C or LISP (which I'd like to have) provided that there are predefined hooks for C and/or LISP in Excel.

    Aladin

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    named formula...

    thanks Aladin



    My question now is that when I do this exact named formula in sheet 1, it works fine in any cell......

    when I try it in sheet 2, nothing happens

    looking at the named formula, it carries the =sheet1! precursor

    is there any way to make it "relative" within different sheets ?

    surely such a great weapon isn't confined to the sheet it was concieved on ?

    many thanks
    Chris

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-27 12:23, Chris Davison wrote:
    named formula...

    thanks Aladin



    My question now is that when I do this exact named formula in sheet 1, it works fine in any cell......

    when I try it in sheet 2, nothing happens

    looking at the named formula, it carries the =sheet1! precursor

    is there any way to make it "relative" within different sheets ?

    surely such a great weapon isn't confined to the sheet it was concieved on ?

    many thanks
    Chris
    Hi Chris:
    The NAMED FUNCTION, as Aladin expalined is an interesting one. You said you used the NAMED FUNCTION as SheetA1!*1.25
    If it is named this way you can go to any worksheet in that workbook and key in anywhere
    =Addtax ... it will result in sheet1!A1*1.25

    However if you want it to read value of cell from the related worksheet, modify the NAMED FUNCTION to read as !A1*1.25

    Now if you go to sheet1 and have entry 6 in in cell A1
    Then keying -in anywhere in sheet1 will give you 6*1.25=7.5

    However if you go to sheet2 and have 4 in cell A1
    then keying-in Addtax anywhere in that sheet will now give you 4*1.25=5

    soreplacing the sheet! with ! normalizes the sheet name

    I apologize for the long winded description!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

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

    Default

    Hi Chris

    Sounds like ive bben ripped apart! erm..

    But

    Of cause just as you would add A1 in one shhet to A2 in shhet 2, all the same.... cant recall if anchers as you call em i call em fixed are needed,, poss not try it...

    Still call em UDF but thats my way and sure as you have seen call em XYZ we all know em the same.. maybe a street south london term needed if badly spelt one... UDF in open play non VBA that is... i must send you some your see what i mean.

    VERY powerful..

    same way we name cell ie VAT and put vat in cell and it calculates.. same idea all time saving and short cutting.

    Take care mate...


    Free Excel based Web Toolbar available here.

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

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,657
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-27 12:23, Chris Davison wrote:
    named formula...

    thanks Aladin

    [img]/board/images/smiles/icon_smile.gif[/img]

    My question now is that when I do this exact named formula in sheet 1, it works fine in any cell......

    when I try it in sheet 2, nothing happens

    looking at the named formula, it carries the =sheet1! precursor

    is there any way to make it "relative" within different sheets ?

    surely such a great weapon isn't confined to the sheet it was concieved on ?

    many thanks
    Chris
    [img]/board/images/smiles/icon_smile.gif[/img]
    Chris,

    Start with an empty WB.
    Activate B1 in some sheet.
    Activate Insert|Name|Define.
    Enter addtax as name in the Names in Workbook box.
    Enter as formula in the Refers to box:

    =!A1*1.25

    Click OK.

    Put anywhere in any sheet a number and next to it:

    =addtax

    Aladin


  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yogi and Aladin,

    great stuff ! thanks a million

    Jack,

    HIya mate - I call it "anchor" cos of the $ sign - it looks like a ship's anchor


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
  •