Click here to Skip to main content
13,198,678 members (49,121 online)
Rate this:
Please Sign up or sign in to vote.
I wrote the module "Table2Excel2()" to replace "Table2Excel1()" to improve the running speed.

However, the data loaded to Excel sheet by module "Table2Excel2()" is same to that of "Table2Excel1()" but is started 1 more row & column (cell P4 but expect O3). I use the code Debug.Print to ensure the data of LTable & MTable are the same. Please advise. Many thanks.
Public LTable(290000, 11) As Variant	'LTable() filled by other module
Sub Table2Excel1()
    Dim RowPtr As Long
    Dim i As Integer
    With Sheets(1)
        For RowPtr = 3 To 290000
            For i = 1 To 8
                .Cells(RowPtr, i + 14) = LTable(RowPtr, i)
            Next i
        Next RowPtr
    End With
    Debug.Print LTable(22, 8) & ","; LTable(3, 2)	'check content of LTable()
End Sub

Public MTable As Variant	'MTable() filled by other module
Sub Table2Excel2()
    Sheets(1).Range("o1", "v" & 290000) = MTable
    Debug.Print MTable(22, 8) & ","; MTable(3, 2)	'check content of MTable()
End Sub
Posted 29-Aug-12 7:36am
Updated 29-Aug-12 8:04am
Kenneth Haugland 29-Aug-12 14:30pm
WHy did you expect one to be faster than the other?
tv1owner 29-Aug-12 15:08pm
Hi Kenneth Haugland,
The module "Table2Excel2()" run faster since it doesn't write to the cell 1 by 1. Please note.

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

First of all, read about MS Excel's objects[^], because Sheet object and Worksheet object is not the same.

The best programming practice is to use code with context. To explain what i mean, do it:
1) Open or create few MS Excel Workbooks,
2) Activate first workbook and run this code:
For i = 1 To 30
    Sheets(1).Range("A" & i) = i
Next i

3) Then activate another workbook and run above code
4) and so on...

What kind of results you can expect?
Each first worksheet for each workbook is filled in with values 1 to 30 in column A.

To properly load data in to correct worksheet, you need to do something like this:
Dim wsh As Worksheet
Set wsh = ThisWorkbook.Worksheets(1)
wsh.Range("A1") = 5

No matter how many times you'll be run the above code and no matter which workbook is active, the above code is running with context and insert the data into correct workbook/worksheet (only into this workbook where the code is placed).

I do not understand what you are trying to do, but if you update your question (use Improve question widget) and explain the exact problem, i'll upgrade my solution. Please, provide more details about used arrays and stored data.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.171020.1 | Last Updated 29 Aug 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100