Databases
StackOverflow2013
Postsdbo
POExcel VBA: Writing an array to cells is very slow
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel VBA: Writing an array to cells is very slow
    primarykey
    data
    text
    <p>I am working with VBA in Excel to retrieve some information from the Reuters 3000 Database. The data I retrieve comes as a bidimensional array consisting of one column holding dates and other column holding numeric values.</p> <p>After I retrieve the information, a process that takes no more than 2 seconds, I want to write this data to a worksheet. In the worksheet I have a column with dates and several other columns with numeric values, each column containing values of a same category. I iterate over the rows of the array to get the date and numeric value and I keep those in a variable, then I search for the date on the date column of the worksheet and after I've found the date I write the value. Here is my code:</p> <pre><code>Private Sub writeRetrievedData(retrievedData As Variant, dateColumnRange As String, columnOffset As Integer) Dim element As Long: Dim startElement As Long: Dim endElement As Long Dim instrumentDate As Variant: Dim instrumentValue As Variant Dim c As Variant: Dim dateCellAddress As Variant Application.ScreenUpdating = False Sheets("Data").Activate startElement = LBound(retrievedData, 1): endElement = UBound(retrievedData, 1) Application.DisplayStatusBar = True Application.StatusBar = "Busy writing data to worksheet" For element = startElement To endElement instrumentDate = retrievedData(element, 1): instrumentValue = retrievedData(element, 2) Range(dateColumnRange).Select Set c = Selection.Find(What:=instrumentDate, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not c Is Nothing Then c.offset(0, columnOffset).Value = instrumentValue End If Next element Application.DisplayStatusBar = False End Sub </code></pre> <p>My problem is that this process is very slow, even if I have only 5 rows in the array it takes about 15 seconds to complete the task. As I want to repeat this process several times (once per each set of data I retrieve from the database), I would like to decrease the execution time as much as possible.</p> <p>As you can see, I am disabling the update of the screen, which is one of the most recurrent actions to improve performance. Does anybody have a suggestion on how I can further decrease the execution time?</p> <p>PS. I know the data retrieval process does not take much because I already tested that part (displaying values on a MsgBox as soon as the data has been retrieved)</p> <p>Thanks in advanced.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USOctavio
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhy is it possible for you take the entire range and have it in a 2D ARRAY? Then do the proecessing within the code and just dumb it to the range? Can you sort of show me a pic of your data sheet, I will give it try.
      singulars
      1. POExcel VBA: Writing an array to cells is very slow
      1. USbonCodigo
    2. CODo you have formulas in the sheet? You might want to turn calculations off if you do. That has caused me heartburn in the past. Maybe you can create a hashtable for your values and populate the list from a linear array? I suspect the search is what is taking forever.
      singulars
      1. POExcel VBA: Writing an array to cells is very slow
      1. USBmo
    3. COUsing Find() in a loop can sometimes be slow. Loading up a Dictionary with instrument names as the keys and the range for each instrument as the value will be much faster if you have many cells to find. And you should always avoid selecting ranges in your code: you can just do this `c.Offset(0,colOffset).Value=instrumentValue`
      singulars
      1. POExcel VBA: Writing an array to cells is very slow
      1. USTim Williams
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload