Printing a 2 dimensional array

**parasbshah** · 07-09-2011, 03:38 PM

Hello,

I am fairly new at VBA Programming for Excel. I would like to print a 2D array (matrix if you will)
to a text file. The 2D array is variant in type as it consists of both string and text. I would like the
array to look as below:

$ CASE COLD NOISE COLD NOISE COLD SCUFF DURABILITY HOTSCUFF
SPEED 1800 2800 4200 6200 6200
BHPE 5000.0 10000.0 36000.0 65900.0 65900.0
FHPE 3000.0 5000.0 20000.0 70000.0 70000.0

I am using the follwing code:

   For iRow = 1 4
      For iCol = 1 To 6  
         Print #IfileNum, LoopData(iRow, iCol)   ' Data is already stored in LoopData matrix
      Next iCol 
   Next iRow

However using this code prints in successive lines as below:

$ CASE
COLD NOISE
COLD NOISE
COLD SCUFF
.....
.....
And So on instead of the column format as I want. How do I get VBA to print as shown above in a column format.

Thanks

Paras

**Mordred** · 07-09-2011, 03:56 PM

Perhaps switching the order of the arrays from

For iRow = 1 4
      For iCol = 1 To 6  
         Print #IfileNum, LoopData(iRow, iCol)   ' Data is already stored in LoopData matrix
      Next iCol 
   Next iRow

to

Sub parasbshah()
    For iCol = 1 To 6
        For iRow = 1 To 4
            Print #IfileNum, LoopData(iRow, iCol)   ' Data is already stored in LoopData matrix
        Next iCol
    Next iRow
End Sub

This is not tested at run-time

**parasbshah** · 07-09-2011, 04:07 PM

Mordred,

Thanks for the suggestion. Howver that did not work. What it just did was print the first column of the array in successive lines and then started the second column and so on (as shown below)

$ CASE
SPEED
BHPE
FHPE
COLD NOISE
1800
5000
3000
....
....
And so on

**watersev** · 07-09-2011, 05:00 PM

hi, parasbshah, it can be done this way

Sub test()
x = [a4:b9] 'A4:B9 has data table
Set fs = CreateObject("Scripting.FileSystemObject"): Set a = fs.CreateTextFile("c:\testfile.txt", True)
For i = 1 To UBound(x): a.WriteLine x(i, 1) & "   " & x(i, 2): Next: a.Close
End Sub

**parasbshah** · 07-09-2011, 05:37 PM

Hi WaterSev,

Thanks for your suggestion. However in my case the array "x" is variable in size depending on the number of Columns entered by the user. So the statement

For i = 1 To UBound(x): a.WriteLine x(i, 1) & " " & x(i, 2): Next: a.Close

will not work for me since I would not know beforehand how many times to concatenate x(irow,icol). I need a logic which will automatically adjust to the number of columns in the array x.

Do you have any suggestions?

Thanks

Paras

**protonLeah** · 07-09-2011, 06:43 PM

Try:

Option Explicit

Sub parasbshah2()
    Dim LineOut As String, _
        iCol    As Long, _
        iRow    As Long, _
        fs, _
        FileOut
        
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set FileOut = fs.CreateTextFile("c:\testfile.txt", True)
    For iRow = 1 To 4
        For iCol = 1 To 5
            LineOut = LineOut & loopdata(iRow, iCol) & Chr(9)
        Next iCol
        LineOut = LineOut & loopdata(iRow, 6)        
        FileOut.WriteLine LineOut
        LineOut = ""
    Next iRow
End Sub

Chr(9)=Tab

**watersev** · 07-09-2011, 06:45 PM

Sub test()
x = [a4:c9] 'A4:C9 has data table
Set fs = CreateObject("Scripting.FileSystemObject"): Set a = fs.CreateTextFile("c:\testfile.txt", True)
delim = "   "
For i = 1 To UBound(x)
    For n = 1 To UBound(x, 2)
        temp = temp & x(i, n) & delim
    Next
    a.WriteLine Left(temp, Len(temp) - 3): temp = ""
Next: a.Close
End Sub