+ Reply to Thread
Results 1 to 11 of 11

Writing into Excel worksheet from custom VBA function (not sub)

  1. #1
    Registered User
    Join Date
    12-24-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    Question Writing into Excel worksheet from custom VBA function (not sub)

    0 down vote favorite
    1


    I would greatly appreciate your assistance in helping me write data from a custom VBA function into an excel worksheet. I have been able to write from a custom VBA subroutine but get an error whilst executing a VBA function. In the sample code, "Sub write 2" and "Sub write 3" work just fine. But "Function test 2()" generates "Value!" in its cell and write3 isn't executed.

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 12-24-2013 at 10:51 PM. Reason: Added Code tags

  2. #2
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Hi,

    What are you trying to accomplish here? What does the function do? I am trying to understand it. Also if what you want is a function that would act just like SUM or AVERAGE from the worksheets you need to return a value, that's whats makes a Function a function and differentiates it from a sub

    without understanding your code and just adding parts I think you are missing you probably want to try something like this:
    Please Login or Register  to view this content.
    Thanks

  3. #3
    Registered User
    Join Date
    12-24-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Hello Fredlo:
    I had just posted sample codes to illustrate my point. Did you test your edited version of "write2"? My bet is this would not work. Excel is permitting changing of cell values from a VBA subroutine but NOT permitting changing of Cell values from a VBA function.
    So, that's my question - how to modify a cell value from a VBA function?

  4. #4
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Quote Originally Posted by fredlo2010 View Post
    What are you trying to accomplish here?
    What does the function do?
    Also if what you want is a function that would act just like SUM or AVERAGE from the worksheets you need to return a value, that's whats makes a Function a function and differentiates it from a sub
    I tested your code and it works, I mean I get no errors so it works. If you answer my questions above I might be able to help you more.

    To modify a cell value from the function just do it as in regular code
    Please Login or Register  to view this content.
    Of course this is not the correct use of a function.

    Thanks

  5. #5
    Registered User
    Join Date
    12-24-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Hello Fredlo!
    Thank you. This is very strange. When I run my original code, I get "Value!" in the cell where I run "test2()". I'm running MS Excel 2010 on Windows 7 Home Premium in a Parallels 9 Desktop environment on OS X 10.7.5. What OS are you using?

    I've a very long function and at the end of it, I'm trying to write values in an Excel sheet. The rest of the function works fine - I've tested it through break points. Now, I'm at at the last step - trying to write the computed values onto the excel sheet cells. And am stuck!

  6. #6
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    what do you mean when you run it? when you try to use it from the Sheet right?

    You will get Value! because the Function is not returning anything. Look at my post #2 and you will see in my sample code whats missing.

    Can you provide the original code and sample of the workbook perhaps with what you need to do?

    Thanks

  7. #7
    Registered User
    Join Date
    12-24-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Excel Help.xlsm
    If you see the original "test 2" that I wrote, test 2 = 1#, so, yes, the function is returning a double. So, I expect to see 1.0 in the cell on the Excel sheet where typed in "=test2()". Unfortunately, I see "Value!".

    I need to use the format Cell(a,b).Value not Range("a1") format as I change the cell values through a loop [Post 1, see Sub Write 2()].

    I've attached the excel sheet below. Please advise if you can see it or if I should email it directly to you?
    In the sheet, I've highlighted the cells as red and put a " ' " before the functions to prevent them from running before you have had an opportunity to view the file.

  8. #8
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Hi,

    Apparently there is n issue here. I am not aware of whats going on but it you remove "Call write3" the function works perfectly.

    I am sorry I cannot help. One more question why is it that you want that code to be activated by a function? Why not use a Sheet_Calculate event if what you are looking for is the code to run every time the formula changes value.

    Thanks

  9. #9
    Registered User
    Join Date
    12-24-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Hello:
    I'm not sure if you meant "no" issue or "an" issue as the text only said "n issue".
    What I'm trying to accomplish is the following:
    At a given row:
    1. Read data from that row and all preceding rows
    2. Feed that row # alongwith the above info into the function
    3. Function does data manipulation
    4. Function outputs results
    I'm not familiar with "Sheet Calculate". Can you please elaborate?
    I thought of a few alternate solutions:
    1. Instead of writing to excel sheet, write data from function into a text file
    2. Instead of using a function, use a subroutine.
    However, my question with solution # 2 is how to indicate the current row # to the subroutine?

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

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    It really sounds like you want a UDF. You cannot do something like "Range("A2").value=6" from a UDF. A function returns values to the spreadsheet through its name. It is somewhat unfortunate that you are trying something so complicated for your first UDF.

    As starting place, I would first suggest you learn how to writ a simple UDF that returns one value to the spreadsheet. Something like:
    Please Login or Register  to view this content.
    called from the spreadsheet as =test1(A1,A2,A3)

    What makes your question more complicated is that, in addition to the calculations you need to do, it sounds like you want the function to return multiple values to the spreadsheet. A UDF is easiest to program if it only returns one value. If the UDF must return multiple values, then you need to program it as an array function. To do this, you need to dimension "result" as an array, store each computation in this array, then return the array to the spreadsheet:
    Please Login or Register  to view this content.
    . In the spreadsheet, this will need to be entered as an array function (ctrl-shift-enter -- see the help files for other array functions like LINEST() or TRANSPOSE() for further examples of entering array functions) across the desired number of cells (my example returns 2 values). Note that, using a single dimensional array, the output will be a horizontal array of consecutive cells, so take that into consideration.

    This is not a new topic. You might review previous topics like this one: http://www.excelforum.com/excel-prog...tion-call.html
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  11. #11
    Registered User
    Join Date
    12-24-2013
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Hello @MrShorty:
    Thank you. I had not thought of using an array, so, I do appreciate your insights.
    Actually, the solution lies in using a Windows timer to bypass the inherent limitation of Excel preventing a UDF from writing into multiple cells. However, I've concluded a more elegant way to transfer data would be to use a subroutine.

  12. #12
    Valued Forum Contributor fredlo2010's Avatar
    Join Date
    07-04-2012
    Location
    Miami, United States
    MS-Off Ver
    Excel 365
    Posts
    762

    Re: Writing into Excel worksheet from custom VBA function (not sub)

    Quote Originally Posted by Drsja View Post
    Hello @MrShorty:
    Thank you. I had not thought of using an array, so, I do appreciate your insights.
    Actually, the solution lies in using a Windows timer to bypass the inherent limitation of Excel preventing a UDF from writing into multiple cells. However, I've concluded a more elegant way to transfer data would be to use a subroutine.
    I am so glad you got it. Can you share the code with us. I cant wait to see it

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do I run a custom function only when a worksheet is selected?
    By jack.x in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-18-2012, 01:59 AM
  2. Worksheet as custom function?
    By burro in forum Excel General
    Replies: 3
    Last Post: 11-10-2011, 08:14 PM
  3. Custom Function from worksheet result
    By tJasC3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-04-2008, 04:24 AM
  4. add worksheet in custom function
    By djoella in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-10-2007, 05:53 PM
  5. [SOLVED] Activate a Worksheet from a Custom Function ??
    By monir in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-17-2005, 02:06 PM

Tags for this Thread

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