1

I have recently been looking into Arrays to write data faster into Excel for reports.

I have come across this, VBA pasting 3 dimensional array into sheet, and it seemed fit to what I want to do. However, I want to do it in only one (1) sheet.

Dim arr(1 To 3)

Dim a As Integer
Dim x As Integer
Dim y As Integer

For a = 1 To 3
    ReDim inner(1 To 5, 1 To 5)

    'don't worry...makes a copy
    arr(a) = inner

    For x = 1 To 5
        For y = 1 To 5
            arr(a)(x, y) = a * x * y
        Next
    Next

    Sheets(a).Select    
    Range(Cells(1, 1), Cells(5, 5)) = arr(a)
Next

Is it possible to output arr into excel range without looping? Something like:
Range(Cells(1, 1), Cells(5, 5*3)) = arr

Expected Output:

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

1 1 1 1 1 - 2 2 2 2 2 - 3 3 3 3 3

I tried doing it buy I got #N/A on my cells as outputs

Community
  • 1
  • 1
Sancho Almeda
  • 151
  • 2
  • 14
  • it isn't clear to me what you want to do. The current code does write the ranges without loops, the loop is being used to write to different sheets – brettdj Jun 20 '13 at 08:59
  • @ brettdj I am sorry. I think it may not be clear what I wanted so I edited the 1st part of the question. – Sancho Almeda Jun 20 '13 at 09:03
  • That is a bit clearer, but why do you do you expect the first 3 values of `arr(1)(1)` to be output in the manner above? – brettdj Jun 20 '13 at 09:18
  • @brettdj Honestly, this is my first time doing these type of array which is rendering me a bit at a loss on how to phrase my question so I apologise. Basically, let's say I have 3 5x5 arrays. Array1 has the values of 1's and Array2 has the values of 2's and so on. I want to write them in an excel file horizontally without looping. Is that possible? – Sancho Almeda Jun 20 '13 at 09:28
  • `Range("A1:E5")` will be Array1; `Range("F1:J5")` will be Array2 and so on. – Sancho Almeda Jun 20 '13 at 09:38

2 Answers2

1

You can do this all with arrays as below

This line

Sheets(1).[a1].Offset(0, UBound(inner) * (lngCnt - 1)).Resize(UBound(inner, 1), UBound(inner, 2)) = arr(lngCnt) says

  • Sheets(1).[a1] .... Start at Sheet1 A1
  • Resize(UBound(inner, 1), UBound(inner, 2)) ... Offset A1 for each subsequent loop by the size of inner (ie 5, so the second loop works on F1, the third on K1)
  • Resize(UBound(inner, 1), UBound(inner, 2)) ... dump to a range equal to the size of inner (ie 5*5)

code

Dim arr(1 To 3)
Dim a As Long
Dim x As Long
Dim y As Long
Dim lngCnt As Long

For a = 1 To 3
    ReDim inner(1 To 5, 1 To 5)
    arr(a) = inner
    For x = 1 To 5
        For y = 1 To 5
            arr(a)(x, y) = a * x * y
        Next
    Next
Next

For lngCnt = 1 To UBound(arr)
    Sheets(1).[a1].Offset(0, UBound(inner) * (lngCnt - 1)).Resize(UBound(inner, 1), UBound(inner, 2)) = arr(lngCnt)
Next

enter image description here

brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Thank you for the answer. I have ended up with the same conclusion but your loop at the end is much better than what I have came up with as it takes into account quite a few things. Thanks again. – Sancho Almeda Jun 20 '13 at 14:31
0

Answer after your edited question.

 Dim sheetNo As Integer
   sheetNo = 1
   Sheets(sheetNo).Select

   Dim startRow As Integer
   Dim endRow As Integer
   Dim startCol As Integer
   Dim endCol As Integer
   Dim totCols As Integer
   Dim lastCol As Integer
   Dim firstCol As Integer
   totCols = 5
   startRow = 2
   endRow = 5
   firstCol = 3
   For curRow = startRow To endRow
      lastCol = firstCol
      For a = 1 To 3
         startCol = lastCol + 1
         endCol = startCol + totCols
         For curCol = startCol To endCol
           ActiveSheet.Cells(curRow, curCol).Value = a
         Next
         endCol = endCol + 1
         If (a < 3) Then
            ActiveSheet.Cells(curRow, endCol).Value = "-"
         End If
         lastCol = endCol
      Next
   Next
varocarbas
  • 12,354
  • 4
  • 26
  • 37
  • 1
    Thanks for the reply varocarbas. As brettdj pointed out, I was a bit unclear on what I want to do. I would like to simply output **arr** in a range in one sheet without looping. – Sancho Almeda Jun 20 '13 at 09:08
  • Sorry, I have replied without understanding properly what you want. I will come back to you in brief with a solution working properly. – varocarbas Jun 20 '13 at 09:16
  • There you have your code. I have preferred to change the approach: you don't need to rely on arrays for a so simple case, you just need to make sure that the loops perform the kind of actions you want. Bear in mind the differences between values of an array and dimensions of an array because from this code I am not sure if you have these ideas completely clear. – varocarbas Jun 20 '13 at 09:40
  • @varacarbas thank you for the alternative and effort you have put to this. This is a good piece of code for me to study. – Sancho Almeda Jun 20 '13 at 14:28
  • You are welcome, Sancha Alameda :) PS: I didn't put any effort, it took me just a couple of minutes. I would have put more effort on building a system based on 2D arrays; that's why my recommendation: why relying on arrays for a so simple problem? – varocarbas Jun 20 '13 at 14:48
  • This is because I have created a program that writes data into an excel file. These data are roughly 13x6 in table per person for 30 persons. Those 30 persons are divided into groups of 10. The tables in each group are added into another 13x6 table which is designated as the supervisor of each group. The problem arises when I compute the tables as I write it in excel in which it is very slow. I have read somewhere that this is caused by massive traffic between VBA and Excel and the solution is to write it as "chunks" of data via Arrays. – Sancho Almeda Jun 21 '13 at 02:00
  • The code I proposed does two actions (reading inputs and writing them to Excel). Your code does three (reading inputs, writing them to a 2D array and then writing this array to Excel). Why you think that this intermediate step is required at all? Reading/writing from Excel is a high-time-consuming task and thus arrays (although ideally 1D arrays) might be necesary to speed up cell-to-cell comparisons, for example. But if you don't need that, don't use it. Rely always on the simplest option to accomplish what you want: the solution shouldn't increase the complexity of the problem. – varocarbas Jun 21 '13 at 07:45
  • 1
    But that is the problem. I need that Cell to Cell comparison since the only data I have would be from the subordinates. This data are then added to get the data for the Supervisors. And then the data for supervisors gets added to get data for program directors. Then take into account that there are 6 supervisors with 12 personnel each. I could have always changed it so that each data are processed before it is written in excel thus reducing cell to cell comparison but this is an old software and building a new software would be much better cost-wise and efficiency-wise. – Sancho Almeda Jun 22 '13 at 07:46
  • OK. Then your approach is fine (unless there is too much data to deal with, in which case you should better rely on temporary files); I thought that your problem was on the lines of the asked question. Next time perhaps you should include details of the whole problem in your question, such that someone could advise you on all the fronts (the specific problem, but also the best way to face the whole situation). – varocarbas Jun 22 '13 at 07:54