+ Reply to Thread
Results 1 to 7 of 7

VBA User Designed Functions (UDF) - Renaming array functions

  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    7

    VBA User Designed Functions (UDF) - Renaming array functions

    Function SpecialFunctionX(RngData As Range, dblSubtractItem As Double, dblConstant As Double) As Double
    With Excel.Application
    SpecialFunctionX = .Max(.Sum(RngData) - dblSubtractItem, dblConstant)
    End With
    End Function


    Function SpecialFunctionY(RngData As Range, dblConstant As Double) As Double
    With Excel.Application
    SpecialFunctionY = .Min(.Sum(RngData), dblConstant)
    End With
    End Function


    Good day, beautiful people of the forum,

    I recently tested the VBA code above and it returns an error, #value!

    To give you a better idea of what Im doing, I basically just copied and pasted the VBA code provided to me by an acquaintance, into excel, clicked on Visual Basic > Insert> Module. I have a workbook where I use =MAX(SUM and=MIN(SUM many times. The only thing that changes is the range.

    I basically went in to all my cells deleted =MAX(SUM and=MIN(SUM then replacing them with =SpecialFunctionX and =SpecialFunctionY while keeping all existing ranges like (G12:G45)-G46,0), (G12:G45),15) , (G50:G88)-G89,0) (G50:G88),15) .etc

    So instead of =MAX(SUM(G12:G45)-G46,0) it would be =SpecialFunctionX(G12:G45)-G46,0).
    And instead of =MIN(SUM(G12:G45),15) it would be =SpecialFunctionY(G12:G45),15)

    I also use ranges like =MAX(SUM(F11,F12,F13)-F14,0) or =MIN(SUM(F35,G35,H35),15).

    I want to be able to go into my existing cells after pasting the VBA code then swap out the existing text =Max(Sum for =SpecialFunctionX and =Min(Sum for =SpecialFunctionY and have them work like the original array function.

    Thank you all, any assistance would be greatly appreciated.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,887

    Re: VBA User Designed Functions (UDF) - Renaming array functions

    Per forum rules, you should wrap your function code in code tags. The forum rules button at the top of the page will explain how to if needed.

    The #Value error indicates a significant failure in the process of calling the function. In particular, the code calls for three arguments to the function, but the function call (with the mismatched parantheses and all) is only sending one argument to the function. This will prevent the function from executing and will trigger the value error. The function call needs to look like specialfunctionx(g12:g45,g46,0).

    I don't see a simple Find/Replace being able to do make this kind of substitution, because each find/replace will need to replace the entire text of the function. It will not work to simply replace a part of the function text.

    In many ways, I don't see a UDF being all that useful for this kind of thing. What is your reason for wanting to incorporate the UDF into these spreadsheets?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Registered User
    Join Date
    07-03-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA User Designed Functions (UDF) - Renaming array functions

    Thank you for letting me know of the forum rules. I still have a lot to learn about excel and my knowledge of VBA is severely limited. My employer, wants me to be able to rename array functions. However, in this case I am merely calling it SpecialFunctionX and SpecialFunctionY because the functions haven't been given permanent names yet.

    I know it's a lot to ask, but if someone could provide me with a VBA code I can copy and paste and have it function appropriately and easily that would be greatly appreciated.

    Thanks to any and all who can help, this is my first request on this forum and I hope I will be able to return the favor to other forum members soon.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: VBA User Designed Functions (UDF) - Renaming array functions

    At a glance, the VBA is fine (if of limited value), it's the formula, as MrShorty pointed out:

    =SpecialFunctionX(G12:G45, G46, 0)
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    07-03-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA User Designed Functions (UDF) - Renaming array functions

    Ok assuming the VBA code is correct, how would I restructure the following

    =MAX(SUM(G7:G21)-G23,0) to become =SpecialFunctionX
    and
    =MIN(SUM(G7:G21),15) to become =SpecialFunctionY The 15 represents the capped total of hours an individual can work.

    I'm assuming my formula structure is off somewhere.


    Quote Originally Posted by shg View Post
    At a glance, the VBA is fine (if of limited value), it's the formula, as MrShorty pointed out:

    =SpecialFunctionX(G12:G45, G46, 0)
    Last edited by hbsonly; 07-03-2013 at 01:53 PM.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677

    Re: VBA User Designed Functions (UDF) - Renaming array functions

    I don't understand your question. You have to change the formula from what you have to what MrShorty and I showed you.

  7. #7
    Registered User
    Join Date
    07-03-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA User Designed Functions (UDF) - Renaming array functions

    Thanks for the help, it actually pushed me in the right direction I figured it out, more or less. I might have to ask more questions later, but thanks for now. I'm extremely grateful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1