Range update does not work from Function in Module

  • I have spent the past 2 days trying to figure this, but finally decided to seek help here. This must be a straightforward query for the VBA experts here.

    I create an Empty Excel. Then go to the Code Editor, create a Module and define a Subroutine in it like this


    Code
    1. Sub TestFromSub()
    2. Range("B2") = "Helllo world. Called From Subroutine"
    3. End Sub


    I invoke the subroutine using Alt-F8 and the code works as intended. The text "Hello World..." appears in cell B2 on my sheet.


    Next I define a function in the same Module as follows


    Code
    1. Public Function TestFromFunction() As Integer
    2. Range("B2") = "Hello world. Called from Function"
    3. TestFromFunction = 1
    4. End Function


    I invoke this Function in my Excel file in cell A1 by defining the value of A1 as "=TestFromFunction()"


    At this point I get a #VALUE error in cell A1.


    The same code which works as a Sub DOES NOT work as a function in VBA.


    What am I missing? Any help/pointers is appreciated in advance. I am using Excel 2007

  • A UDF called from a cell cannot assign a value to another cell.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Yes, you can work around it, but in my opinion, it's a bad idea. I can't really imagine any situation where that would be the best solution. Why do you need to do it?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Here is the use case for why I need to do it.


    I am writing a UDF which will use an API to pull data from the Cloud. The data will be returned as a Json string which the function will convert into a 2D Array. The number of colums in the array will be fixed whereas the number of rows will vary, depending on the input parameter to the API.


    Now I have two choices to return the data back to the Excel sheet from the UDF


    1. Return the array and the use Ctl+Shift+Enter in Excel to mark the cells where the array will be displayed
    2. Have the UDF print the array to Excel, given some cell as a starting point


    Since the number of rows returned by the API are variable I cannot use option 1. Hence I am constrained to use option 2.

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.