+ Reply to Thread
Results 1 to 9 of 9

Making A Function from a Sub

  1. #1
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Making A Function from a Sub

    Good Morning,
    I have this so called function that works inside my userform, However I have decided to try to get the results in a cell on my worksheet.Can someone help me change the script below to work.

    For example in cell B66 I would put something like = mystring1
    The problem is I get a name error when I try that method now.

    Any Ideas? Thank You, Mike



    Please Login or Register  to view this content.
    This is the sub from the userform that calls all the functions. I have a sevearl of these functions that refer to different ranges on my worksheet.

    Please Login or Register  to view this content.
    Last edited by realniceguy5000; 07-13-2011 at 09:01 AM.

  2. #2
    Valued Forum Contributor mohd9876's Avatar
    Join Date
    05-04-2011
    Location
    Amman, Jordan
    MS-Off Ver
    Excel 2010
    Posts
    426

    Re: Making A Function from a Sub

    It is giving you the name error because you are not giving its input argument, like this
    Please Login or Register  to view this content.

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Making A Function from a Sub

    Please Login or Register  to view this content.
    PS. rephrase:
    Please Login or Register  to view this content.
    into

    Please Login or Register  to view this content.
    Last edited by snb; 07-13-2011 at 08:24 AM.



  4. #4
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Making A Function from a Sub

    You'll also probably want to make that function volatile, since it deals with colours, and you are not passing a range to it directly.
    Remember what the dormouse said
    Feed your head

  5. #5
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Making A Function from a Sub

    Thanks Everyone for the help again,

    @snb
    I thought at first this was a typo "IIf(j > 1, "s", ""))"

    What is IIF?

    Thanks Again, Mike


    Here is what I am using.
    in cell "=mystringr()"


    Please Login or Register  to view this content.

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,647

    Re: Making A Function from a Sub

    IIF is the string you have to insert after clicking F1 in the vbeditor.

  7. #7
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Making A Function from a Sub

    romperstomper,

    Just Curious: Could you explain a little more about "make that function volatile"

    Not sure I follow why to or not to use.

    And Dont Laugh, how do you make it volatile?

    Thank You,
    Thank You, Mike

    Some Helpful Hints:

    1. New members please read & follow the Forum Rules
    2. Use Code Tags...Place[code]Before the first line and[/code] After the last line.
    3. If you are pleased with a solution mark your post SOLVED.
    4. Thank those who have help you by clicking the scales at the top right of the post.

    Here...

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Making A Function from a Sub

    because you re using colours as data (bad idea, IMO) and you are not passing the range as an argument to the function, it will not update when the data or colours change. You make it volatile by adding:
    Please Login or Register  to view this content.
    to the function code, or by adding a volatile function (like NOW()) to the end of the function call in the cell. Note: changing cell colours does not cause a recalculation so you won't see the formula update after changing colours until you do something that actually causes the workbook to recalculate.

  9. #9
    Valued Forum Contributor realniceguy5000's Avatar
    Join Date
    03-20-2008
    Location
    PA
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    951

    Re: Making A Function from a Sub

    Great, I Understand completely now...

    Thanks You!!!!

    Mike

+ 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