Posts by Findaway

    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.

    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

    Sub TestFromSub()
    Range("B2") = "Helllo world. Called From Subroutine"
    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

    Public Function TestFromFunction() As Integer
    Range("B2") = "Hello world. Called from Function"
    TestFromFunction = 1
    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