CODEWITHSUNDEEP
arraysvbaexcel
j128
j128

Reputation: 51

VBA function calls VBA sub to print array to worksheet

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?

Upvotes: 4

Views: 1614

Answers (1)

j128
j128

Reputation: 51

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!

Upvotes: 1

Related Questions