VBA function calls VBA sub to print array to worksheet

Question

Currently I have a user-defined function which runs several routines involving the generation of matrices. To check these matrices have been produced correctly I want to put them on a worksheet. The user defined function works fine and I have added the below into it at the place where I want to find out what is in the array:

Call CheckArray(TestArray)

Where 'TestArray' in the above varies depending on the array I want to look at.

The 'CheckArray' subroutine is as follows:

Sub CheckArray(MyArray As Variant)
MatrixRows = UBound(MyArray, 1)
MatrixCols = UBound(MyArray, 2)

MsgBox MyArray(11, 2)
MsgBox "Matrix size = " & MatrixRows & " rows x " & MatrixCols & " columns"

ActiveWorkbook.Worksheets("Check array").[A1].Resize(MatrixRows, MatrixCols) = MyArray
End Sub

Note that I placed the two MsgBox commands in there to check the sub was called correctly and that it was working, which it is. Moreover, it returned the value in the specific location I requested and it also stated the size of the particular matrix I was looking at, so the data does seem to be getting read correctly - the issue is with subsequently writing that from the new sub.

When I include that final line it does not print my array to the worksheet and it also stops the user-defined function from working correctly. Does anyone know why this isn't working?

Can user-defined functions call subs which print to a worksheet or not? Is there a way to fix this?

No they can't (unless you want to start messing around with Timers and/or events). UDFs called from cells can broadly speaking only return values to those same cells. You could however output the array to the immediate window. — Rory, Mar 02 '16 at 14:52
Thanks for the quick response. What do you mean by output the array to the 'immediate window'? — j128, Mar 02 '16 at 14:53
Hi there, I have done much VBA, and this should be possible. Based on your question I cannot tell if you are trying to use the VBA function in a cell, like this in a cell " =CheckArray(TestArray)". If that is so then Rory is correct in that your result will only go to that cell where you put your function as a formula. But you should be able to, via VBA in code specify a range object that can specify which cell to send the result to on any sheet you require. — Palu, Mar 02 '16 at 14:59
Hi Palu. I am indeed using a user-defined function in a cell. Do you mean that I can use the function to change a named range and then print that named range with my matrix in? I'm also interested in how I could view my matrix in the immediate window. — j128, Mar 02 '16 at 15:03
Also, based on the last line of your code, seems like you are trying to output a 2-D array, that is Matrix into one cell. You can put a number or string into a cell but I doubt a Matrix. You need to loop thru the matrix putting each element of the Matrix into a cell here being "A1", replicating the Matrix structure on the worksheet. — Palu, Mar 02 '16 at 15:05
Hi Palu. That last line worked for a matrix I created outside of the function. It seems to be related to it being incompatible with user-defined functions. Regarding printing to the immediate window I found the following was useful: http://stackoverflow.com/questions/14274949/how-to-print-two-dimensional-array-in-immediate-window-in-vba — j128, Mar 02 '16 at 15:11
OK, I could not infer from your question that you were trying to use your function in a cell, I just went with what Rory said. Yes, printing the results to your window is another way to debug, I usually just use MsgBox for that when I want to check for particular value. But when you want to print an array, I would put it on the worksheet. — Palu, Mar 02 '16 at 15:16
Yes, with range object, you can put your results be it values or arrays on to any range of cells you like. It does not have to be a named range. To write this is pseudocode you do this: range(C3:D9) which is 7rows by 2columns matrix. You can also specify named ranges in code. SO you have the cell addressing method and the named range method. So you cannot assign an array into a cell, only values. — Palu, Mar 02 '16 at 15:20
That doesn't work for me - I think because of using a function rather than a macro. I've looked into the immediate window thing a bit more and want to know if there is a way of exporting data FROM the immediate window. There is lots out there about printing TO the immediate window and other files. But I can't do that because of this user-defined function issue. So what I need to know is how to export data that is contained in the immediate window. Currently I'll have to do that manually. — j128, Mar 02 '16 at 15:50

score 1 · Answer 1 · edited May 23 '17 at 12:07

To summarise:

As I am using a user-defined function which is entered in a cell in a worksheet I cannot export data to anywhere but that cell. However, I can view the arrays in the Immediate Window.

In MS Excel 2010 the Immediate Window can be found in the VBA editor (Alt+F11) and then click on View -> Immediate Window (Ctrl+G).

To export my array to the Immediate Window I should enter this into my code after the array I want to view:

Call WriteArrayToImmediateWindow(MyArray)

Where 'MyArray' is the name of my array, whatever that is.

This will then call the 'WriteArrayToImmediateWindow' sub, which is:

Sub WriteArrayToImmediateWindow(arrSubA As Variant)

Dim rowString As String
Dim iSubA As Long
Dim jSubA As Long

rowString = ""

Debug.Print
Debug.Print "The array is: "
For iSubA = 1 To UBound(arrSubA, 1)
    rowString = arrSubA(iSubA, 1)
    For jSubA = 2 To UBound(arrSubA, 2)
        rowString = rowString & "," & arrSubA(iSubA, jSubA)
    Next jSubA
    Debug.Print rowString
Next iSubA

End Sub

Credit for the above goes to User3706920: How to print two dimensional array in Immediate window in VBA?

EDIT:

I decided that viewing the arrays in the Immediate Window wasn't always useful and I needed to view the data comma-separated in Excel. If you want to do the same follow the below instructions:

To export the array to a text file you should enter this into your code after the array you want to view:

Call WriteToFile(MyArray)

Where 'MyArray' is again the name of the array you want to review. The 'WriteToFile macro is as follows:

Sub WriteToFile(arrSubA As Variant)
'To export array to a text file
    'Setup
    Dim FSO As Object
    Dim ofs As Object
    Dim Output As Variant
    Dim rowString As String
    Dim iSubA As Long
    Dim jSubA As Long
    rowString = ""

    'Create file
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = FSO.CreateTextFile("C:\Users\" & Environ$("username") & "\Desktop\Array.txt")
    oFile.Close
    For iSubA = 1 To UBound(arrSubA, 1)
        rowString = arrSubA(iSubA, 1)
        For jSubA = 2 To UBound(arrSubA, 2)
            rowString = rowString & "," & arrSubA(iSubA, jSubA)
        Next jSubA
        If Len(Dir("C:\Users\" & Environ$("username") & "\Desktop\Array.txt")) > 0 Then
            Set ofs = FSO.OpenTextFile("C:\Users\" & Environ$("username") & "\Desktop\Array.txt", 8, True)
        End If
        ofs.WriteLine rowString
        ofs.Close
    Next iSubA
End Sub

To quickly view the output array in Excel without any commas I would recommend assigning the below macro to a button:

Sub OpenArrayFile()
'To open array text file in Excel
    Workbooks.OpenText Filename:="C:\Users\" & Environ$("username") & "\Desktop\Array.txt", Origin:= _
        xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
        , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
End Sub

Hopefully this is useful to others too!

OK, you see the issue is, if you have to print a 2-D array, and want to put all of that into a cell, I doubt you would be able to do that with a function in a cell. Because a function by definition returns a value. You may be able to print a concatenated string of all the values of the Matrix into a cell. But you would have to create a function specifying the return type being a String. A user function used in a cell should be a Function and not a procedure(Sub). — Palu, Mar 02 '16 at 16:32
I just can't imagine why you would want to print so much data, like a Matrix into one cell in the spreadsheet. — Palu, Mar 02 '16 at 16:33
The function actually returns several different values depending on the column it is in. Producing an array for QA was obviously a step too far. Still the above seems to do what I needed. Thanks for your help. — j128, Mar 02 '16 at 16:36

VBA function calls VBA sub to print array to worksheet

1 Answers1