+ Reply to Thread
Results 1 to 12 of 12

How do i write to cells in a function?

  1. #1
    Forum Contributor
    Join Date
    09-21-2012
    Location
    illinois
    MS-Off Ver
    Excel 2010
    Posts
    242

    How do i write to cells in a function?

    I can do it from a Macro but aparently not from a function. below I have included some code I was using to test this. any ideas what I can do to get it to work. This would be very useful to me in many aspects.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    08-13-2012
    Location
    Gardony, Hungary
    MS-Off Ver
    Excel 2003
    Posts
    558

    Re: How do i write to cells in a function?

    Hi,

    as far as I know, a Function returns a single value, so it can't modify cells just like that. You can use that value in other Subs (macros) to fill cells or whatever. You can call the Function from a Sub or you can even make a Sub that has arguments like a Function.

    Edit:

    I'm not sure, but maybe this would work. You start the Starter Sub and it calls the Sub with your code. I didn't change anything, just called it from another macro and used "Sub" instead of "Function".
    Please Login or Register  to view this content.
    Last edited by RHCPgergo; 11-28-2012 at 10:38 AM.

  3. #3
    Forum Contributor
    Join Date
    09-21-2012
    Location
    illinois
    MS-Off Ver
    Excel 2010
    Posts
    242

    Re: How do i write to cells in a function?

    OK I had a feeling I was going to have to call a macro. but I wanted to check before I did that. Thanks.

  4. #4
    Valued Forum Contributor
    Join Date
    08-13-2012
    Location
    Gardony, Hungary
    MS-Off Ver
    Excel 2003
    Posts
    558

    Re: How do i write to cells in a function?

    Not sure if you've seen my edit, maybe it would help.

  5. #5
    Forum Contributor
    Join Date
    09-21-2012
    Location
    illinois
    MS-Off Ver
    Excel 2010
    Posts
    242

    Re: How do i write to cells in a function?

    I was going to try to call a sub from the function. What do you think?

  6. #6
    Valued Forum Contributor
    Join Date
    08-13-2012
    Location
    Gardony, Hungary
    MS-Off Ver
    Excel 2003
    Posts
    558

    Re: How do i write to cells in a function?

    I've never tried that. Is that possible? :D

  7. #7
    Forum Contributor
    Join Date
    09-21-2012
    Location
    illinois
    MS-Off Ver
    Excel 2010
    Posts
    242

    Re: How do i write to cells in a function?

    We will soon see.

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

    Re: How do i write to cells in a function?

    It is not possible.
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Forum Contributor
    Join Date
    09-21-2012
    Location
    illinois
    MS-Off Ver
    Excel 2010
    Posts
    242

    Re: How do i write to cells in a function?

    nope cant do it

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,581

    Re: How do i write to cells in a function?

    If this is a function on a worksheet calling a sub won't work.

    Can I ask why you need a function anyway?

    Why not use the worksheet change event?
    If posting code please use code tags, see here.

  11. #11
    Forum Contributor
    Join Date
    09-21-2012
    Location
    illinois
    MS-Off Ver
    Excel 2010
    Posts
    242

    Re: How do i write to cells in a function?

    Cool I was unaware of that can you give me an example?

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

    Re: How do i write to cells in a function?

    You can call a sub from a udf, but the same restrictions still apply -- the sub cannot alter the value of another cell like you are asking.

    2 basic ideas I see for something like this:

    1) Can you separate the logic for result A and result B? If you can separate the logic for the two results, then you put a function or code a separate udf to obtain result B and put that in cell B rather than having cell A/code A try to do both results.
    2) If result A and result B are too closely interconnected to be separated, then you can write your udf as an array function and have it return both values. This looks like:
    Please Login or Register  to view this content.
    As with any of the built in array functions, the function has to be entered with cntrl-shift-enter, and the results are returned as an array (so typically they end up in adjacent cells). You can conceivably nest the udf inside the INDEX() function to return A or B, if desired.

+ 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