none
How to display an array of column or row data in VBA that does not start at the top of the array.

    Question

  • I have managed to successfully display an array of data from VBA to an Excel sheet column using code such as this:

    Private OutputCol() As String
    ReDim OutputCol(1 To RowCount, 1 To 1)
    OutputCol(1, 1) = 1
    OutputCol(2, 1) = 2
    OutputCol(3, 1) = 3
    OutputCol(4, 1) = 4
    ActiveSheet.Range("C4:C7").Value = OutputCol
    

    However, what I would like to do is instead of displaying the data from the top of the array, or index 1 in this case, I would like to display it starting from index 3 for example.  Is there any way that this can be done?  Right now, I have to move the data from arrays in memory to an output array for this purpose.

    If this can't be done, then I would like to suggest to the VBA/Excel team to provide a way to allow for this in the future.

    Monday, September 20, 2010 12:43 AM

All replies

  • Hi Bob,

     

    You shouldn't have to assign data to another array. Can put it directly to the worksheet.

    Sub test()

    Dim OutputCol()

    Dim RowCount
    Dim i As Long

    RowCount = 20

    ReDim OutputCol(1 To RowCount, 1 To 1)

    For i = 1 To UBound(OutputCol, 1)
      OutputCol(i, 1) = i
    Next

    For i = 3 To UBound(OutputCol, 1)
      'Note i + 1 is the calculated row number
      'In this example starts at 3 + 1 = row 4.
      ActiveSheet.Cells(i + 1, "C").Value = OutputCol(i, 1)
    Next i

    End Sub


    Regards, OssieMac
    Monday, September 20, 2010 2:01 AM
  • Hi OssieMac,

    The point here is that outputting an array to an Excel worksheet is much more efficient than using a loop to output a single line.  According to my speed tests, it is somewhere between 3 to 4 times faster.  According to a web page from Microsoft that I read, they claimed it is more like 50 times faster.  But, I think that was with a previous version of Excel.  Here is the code that I used to test it with:

    Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SystemTime)
    
    Public Sub TestExcelUpdateSpeedB()
     'This sub tests out how quickly Excel can update a column
     Dim OutLoop As Long
     Dim Row As Long
     Dim StartTime As SystemTime
     Dim EndTime As SystemTime
     Dim Price, TotalTime As Double
     Dim StartTimeMSecs, EndTimeMSecs, StartHour, EndHour As Long
    
     Application.Calculation = xlCalculationManual
     Application.ScreenUpdating = False
     Application.EnableEvents = False
     Price = 150.75
     GetLocalTime StartTime
     For Row = 6 To 10005
      ThisWorkbook.Worksheets("Tests").Cells(Row, "b").Value = Row + 0.5
     Next
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
     Application.EnableEvents = True
     GetLocalTime EndTime
     StartHour = StartTime.wHour
     EndHour = EndTime.wHour
     StartTimeMSecs = (StartHour * 3600) + (StartTime.wMinute * 60) + StartTime.wSecond
     StartTimeMSecs = StartTimeMSecs * 1000 + StartTime.wMilliseconds
     EndTimeMSecs = (EndHour * 3600) + (EndTime.wMinute * 60) + EndTime.wSecond
     EndTimeMSecs = EndTimeMSecs * 1000 + EndTime.wMilliseconds
     TotalTime = EndTimeMSecs - StartTimeMSecs
     TotalTime = TotalTime / 1000
     TotalTime = Round(TotalTime, 4)
     ThisWorkbook.Worksheets("Tests").Cells(5, "a").Value = TotalTime
    End Sub
    
    Public Sub TestExcelUpdateSpeedC()
     'This sub tests out how quickly Excel can update 10000 columns all at once.
     Dim OutLoop As Long
     Dim Row As Long
     Dim StartTime As SystemTime
     Dim EndTime As SystemTime
     Dim Price, TotalTime As Double
     Dim StartTimeMSecs, EndTimeMSecs, StartHour, EndHour As Long
    
     Application.Calculation = xlCalculationManual
     Application.ScreenUpdating = False
     Application.EnableEvents = False
     Price = 150.75
     For OutLoop = 0 To 9999
      DArray(OutLoop) = OutLoop + 0.5
     Next
     GetLocalTime StartTime
     'The next line outputs the entire array into the B column without using a loop.
     ThisWorkbook.Worksheets("Tests").Cells(6, 2).Resize(UBound(DArray) + 1).Value = Application.Transpose(DArray)
     '
     Application.Calculation = xlCalculationAutomatic
     Application.ScreenUpdating = True
     Application.EnableEvents = True
     GetLocalTime EndTime
     StartHour = StartTime.wHour
     EndHour = EndTime.wHour
     StartTimeMSecs = (StartHour * 3600) + (StartTime.wMinute * 60) + StartTime.wSecond
     StartTimeMSecs = StartTimeMSecs * 1000 + StartTime.wMilliseconds
     EndTimeMSecs = (EndHour * 3600) + (EndTime.wMinute * 60) + EndTime.wSecond
     EndTimeMSecs = EndTimeMSecs * 1000 + EndTime.wMilliseconds
     TotalTime = EndTimeMSecs - StartTimeMSecs
     TotalTime = TotalTime / 1000
     TotalTime = Round(TotalTime, 4)
     ThisWorkbook.Worksheets("Tests").Cells(5, "a").Value = TotalTime
    End Sub
    
    

    In any case, only allowing data starting from the first index of the array is a very poor way of doing things.  Try running both methods of outputting data to an Excel spreadsheet and you should see the difference.

    Bob

    Monday, September 20, 2010 9:39 AM