VBA Tips - Time Your Code

Can anyone recommend a good source for tips on optimizing VBA code? I'm working on
an application that reads and interprets large text files (3500 lines) and starting to think
about the efficiency of the code inside my read loop.

For example I'm wondering how Excel actually implements access to cells internally. Is a
cell just a variable or part of an array?

If I do something like: range("A1").value = range("A1").value + 1 inside a loop is there a
performance penalty compared with saying: count = count + 1 and then writing this to a
cell outside the loop, eg: range("A1").value = count ?

Many thanks,

Richard.

I've decided I am going to go through code that I have from projects I have completed and I wanted to get some advice on better writing techniques. I'll give two examples that do the same thing (clean my insert page) from all my projects.

Original code:

Code:

Range("InsertClean").Select
Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
Selection.Borders(xlEdgeLeft).LineStyle=xlNone
Selection.Borders(xlEdgeRight).LineStyle=xlNone
Selection.Borders(xlEdgeTop).LineStyle=xlNone
Selection.Borders(xlEdgeBottom).LineStyle=xlNone
Selection.Borders(xlInsideVertical).LineStyle=xlNone
Selection.Borders(xlInsideHorizontal).LineStyle=xlNone
Selection.UnMerge
Selection.RowHeight=15
Selection.Interior.ColorIndex=xlNone

Or, is this a better, smoother way:

Code:

With Selection
    .ClearContents
    .Borders(xlDiagonalDown).LineStyle=xlNone
    .Borders(xlDiagonalUp).LineStyle=xlNone
    .Borders(xlEdgeLeft).LineStyle=xlNone
    .Borders(xlEdgeRight).LineStyle=xlNone
    .Borders(xlEdgeTop).LineStyle=xlNone
    .Borders(xlEdgeBottom).LineStyle=xlNone
    .Borders(xlInsideVertical).LineStyle=xlNone
    .Borders(xlInsideHorizontal).LineStyle=xlNone
    .UnMerge
    .RowHeight=15
    .Interior.ColorIndex=xlNone
End With

I think the second way is easier on the eyes but does it make any difference as far as how the code runs?

Thank Ya Big Big

Hello everyone,

I am looking for some feedback on the code I have written. It is my first attempt at using arrays in code for the purpose of accumulating data, and subesequently using the same data to create a report.

My code below looks at a table that is 10 columns wide and reads all of the data into 10 different arrays - this is where feedback may be useful, should I have used a multi dimensional array (if so show me how please).

After it reads the data into the arrays it looks at the first array and finds the unique items in that array, which are then used to create a nice report outlined by the groupings.

Once again any feedback on ways to improve the code would be great - learning lots.

Thanks

Code:

Option Explicit
Sub investment1()
'Macro to create investment portfolio report - By Gerry on March 9, 2009
    Dim lr As Long, i As Long, j As Long, k As Long, l As Long
    Dim CUname As String, RPdate As String
    Dim grp1() As Variant, grp2() As Variant, grp3() As Variant, grp4() As Variant
    Dim grp5() As Variant, grp6() As Variant, grp7() As Variant, grp8() As Variant
    Dim grp9() As Variant, grp10() As Variant
    Dim hdg() As Variant, hdg1 As Long
    Application.ScreenUpdating = False
'Get the relevant data - 10 columns of data, 1 array for each column
    With Worksheets("Inputs")
        lr = .Cells(.Rows.Count, 1).End(xlUp).Row
        ReDim grp1(1 To lr), grp2(1 To lr), grp3(1 To lr), grp4(1 To lr), grp5(1 To lr)
        ReDim grp6(1 To lr), grp7(1 To lr), grp8(1 To lr), grp9(1 To lr), grp10(1 To lr)

        For i = 8 To lr
        
            grp1(i) = .Cells(i, 1).Value
            grp2(i) = .Cells(i, 2).Value
            grp3(i) = .Cells(i, 3).Value
            grp4(i) = .Cells(i, 4).Value
            grp5(i) = .Cells(i, 5).Value
            grp6(i) = .Cells(i, 6).Value
            grp7(i) = .Cells(i, 7).Value
            grp8(i) = .Cells(i, 8).Value
            grp9(i) = .Cells(i, 9).Value
            grp10(i) = .Cells(i, 10).Value
        Next
    'Get the CU name and report date
        CUname = .Range("A1").Value
        RPdate = Format(.Range("C5").Value, "MMMM DD, YYYY")
    End With

'Get the category headings and count using custom function called UniqueItems
        hdg = UniqueItems(grp1, False)
        hdg1 = UniqueItems(grp1)

Sheets.Add
ActiveSheet.Name = "newSht"


'Set Starting row for report
    k = 7
'Put the data into report format
    For i = 1 To hdg1 'counter for each group heading
        Cells(k, 1).Value = hdg(i)
        Cells(k, 4).Value = "Start"
        Cells(k, 5).Value = "Maturity"
        Cells(k, 6).Value = "Face Value"
        Cells(k, 7).Value = "Amortization"
        Cells(k, 8).Value = "Book Value"
        Cells(k, 9).Value = "Yield"
        Cells(k, 10).Value = "Bond Rating"
        l = k 'Counter for top row of group
            For j = 1 To UBound(grp1) 'search array for data that matches heading
                If grp1(j) = hdg(i) Then
                        k = k + 1
                        Cells(k, 2).Value = grp2(j)
                        Cells(k, 3).Value = grp3(j)
                        Cells(k, 4).Value = grp4(j)
                        Cells(k, 5).Value = grp5(j)
                        Cells(k, 6).Value = grp6(j)
                        Cells(k, 7).Value = grp7(j)
                        Cells(k, 8).Value = grp8(j)
                        Cells(k, 9).Value = grp9(j)
                        Cells(k, 10).Value = grp10(j)
                End If
            Next j
        'Insert Totals
            k = k + 2
            l = k - l - 1
                Cells(k, 1).Value = hdg(i) & " Total"
                Cells(k, 6).FormulaR1C1 = "=SUBTOTAL(9,R[-" & l & "]C:R[-1]C)"
                Cells(k, 7).FormulaR1C1 = "=SUBTOTAL(9,R[-" & l & "]C:R[-1]C)"
                Cells(k, 8).FormulaR1C1 = "=SUBTOTAL(9,R[-" & l & "]C:R[-1]C)"
                Cells(k, 9).FormulaR1C1 = "=sumproduct(--(R[-" & l & "]C[-3]:R[-1]C[-3]),--(R[-" & l & "]C:R[-1]C))/RC[-3]"
                
        'Format display
                With Union(Range(Cells(k, 1), Cells(k, 10)), Range(Cells(k - l - 1, 1), Cells(k - l - 1, 10)))
                    .Font.Bold = True
                    With .Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
                    End With
                    With .Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                        .ColorIndex = xlAutomatic
                    End With
                End With
          'Put two blank rows in between
            k = k + 3
        Next i

'Finish Formatting - make it look pretty
    Range("F:H").NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Range("I:I").NumberFormat = "0.00%"
    Columns.AutoFit
    Columns("A:A").ColumnWidth = 4
    Range("A1").Value = CUname
    Range("A2").Value = "Board Report on Investment Management"
    Range("A3").Value = "Part 4 - Investment Quality"
    Range("A4").Value = RPdate
        With Range("A1:J4")
            .HorizontalAlignment = xlCenterAcrossSelection
            .Font.Bold = True
        End With


ActiveWindow.DisplayGridlines = False
Application.ScreenUpdating = True
End Sub

Hi,

Before posting questions here, I'd like to be able to independently search the wealth of tips, techniques, code, etc on the Web in an organised way. Some sites have excellent indexes of these tricks on their individual sites. Is there any source that pulls this info from these different sites together (preferably in an organised way)?

Failing that, I'd like to know how experts on this site reference their info, because your references seem to be consistently at your fingertips. What sources do you use and how do you sort the wheat from the chaff when answering questions? Here's a couple of indexes. What other indexes do you recommend?
http://www.cpearson.com/excel/topic.htm
http://www.contextures.com/tiptech.html

Many thanks in advance for helping me learn how to fish.

John

Hi all,

I'm new to the forum and couldn't find any good tips for speeding up my program, but I'm hoping there's someone who can help. I'm writing this for a school project in my Heat Transfer class. m and n are coordinates, m range from 1 to 31, n range from 1 to 41 and I need to output a temperature (Tnode) for every (m,n) coordinate. Tau, Time, and Tstep are user inputted, but they can be .225, 20, and .2, respectively. I'm running Excel07/Win7/4GB ram/2.4GHz duo if that matters.

Was hoping someone could look at my poopty code. At the moment it works as it should but would like to speed it up a bit by shortening the code. Most of it was created with the macro recorder but some has been edited from help on this forum. The short of it is the macro opens 5 external spreadsheets on a server and pastes their contents into this single workbook into tabs which are cleared before the import.

Code:

Sub ImportReports()
'
' ImportReports Macro

  Application.Goto Sheets(8).Cells(1, 1)
  Application.DisplayAlerts = False
    Cells.Select
    Selection.ClearContents
    ActiveSheet.Next.Select
    Cells.Select
    Selection.ClearContents
    ActiveSheet.Next.Select
    Cells.Select
    Selection.ClearContents
    ActiveSheet.Next.Select
    Cells.Select
    Selection.ClearContents
    ActiveSheet.Next.Select
    Cells.Select
    Selection.ClearContents
    ChDir "\\temleg01\shared\Project Genesis\CGM\Upstream reporting\Reports"
    Workbooks.Open Filename:= _
        "\\temleg01\shared\Project Genesis\CGM\Upstream reporting\Reports\Upstream - Total Variance vs 09.xls"
    Sheets("IPP Summary").Select
    Cells.Select
    CutCopyMode = False
    Selection.Copy
    Windows("FLATPACK TEMPLATE - Upstream & Optimisation.xls").Activate
    ActiveSheet.Paste
    ActiveSheet.Previous.Select
    Windows("Upstream - Total Variance vs 09.xls").Activate
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    Cells.Select
    CutCopyMode = False
    Selection.Copy
    Windows("FLATPACK TEMPLATE - Upstream & Optimisation.xls").Activate
    ActiveSheet.Paste
    ActiveSheet.Previous.Select
    Workbooks.Open Filename:= _
        "\\temleg01\shared\Project Genesis\CGM\Upstream reporting\Reports\Upstream - Total Variance vs 08.xls"
    Sheets("Coal Summary").Select
    Cells.Select
    CutCopyMode = False
    Selection.Copy
    Windows("FLATPACK TEMPLATE - Upstream & Optimisation.xls").Activate
    ActiveSheet.Paste
    ActiveSheet.Previous.Select
    Workbooks.Open Filename:= _
        "\\temleg01\shared\Project Genesis\CGM\Upstream reporting\Reports\Optimisation - Total Variance vs 09.xls"
    Cells.Select
    CutCopyMode = False
    Selection.Copy
    Windows("FLATPACK TEMPLATE - Upstream & Optimisation.xls").Activate
    ActiveSheet.Paste
    ActiveSheet.Previous.Select
    Workbooks.Open Filename:= _
        "\\temleg01\shared\Project Genesis\CGM\Upstream reporting\Reports\Optimisation - Total Variance vs 08.xls"
    CutCopyMode = False
    Cells.Select
    Selection.Copy
    Windows("FLATPACK TEMPLATE - Upstream & Optimisation.xls").Activate
    Windows("Optimisation - Total Variance vs 08.xls").Activate
    Windows("FLATPACK TEMPLATE - Upstream & Optimisation.xls").Activate
    ActiveSheet.Paste
    Windows("Upstream - Total Variance vs 09.xls").Close
    Windows("Upstream - Total Variance vs 08.xls").Close
    Windows("Optimisation - Total Variance vs 09.xls").Close
    Windows("Optimisation - Total Variance vs 08.xls").Close
    
 ' Import GMCI report
   
    Workbooks.Open Filename:="\\temleg01\shared\Project Genesis\CGM\Upstream reporting\Reports\GMCI Report - Combined PYA.xls"
    Range("A9:X65536").Copy
    Workbooks("FLATPACK TEMPLATE - Upstream & Optimisation").Sheets(5).Cells(7, 3).PasteSpecial Paste:=xlPasteValues
    Windows("GMCI Report - Combined PYA.xls").Close
    
    Application.Goto Sheets(1).Cells(1, 1)

Hey,

Has anybody noticed that all the help sections of the date/time help in excel tips is missing any information that's normally there?

just thought I would mention it.


oliver.

Have searched but could not find what I am looking for. I have a basic Excel test that I need to have saved to a floppy, upon saving I want to find out the elapsed time from the time it was saved (to floppy) to the time the file was saved and closed. The elapsed time can be saved to a cell on the worksheet or on a pop-up window.

Any information or an alternative solution will be helpful. As always thanks in advance.

Alfonso

I need to use a specific Excel template to input timecodes (cue-IN and cue-OUT) and get the duration for each set (cue-OUT minus cue-IN) as well as the grand total of all the duration (total running time). The formula for each row to figure out the duration is given but there is no formula provided to figure out the total running time. Could you kindly take a look at the attached sample file and provide me with the formula for the total duration (orange cell)? I would appreciate it very much. The following are the formulae for two different frame rate (frame per second).

30FPS

=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86401,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),30),"\:00")


24FPS
=TEXT(LEFT(B2,8)-LEFT(A2,8)-(RIGHT(A2,2)>RIGHT(B2,2))/86400,"m:ss")&TEXT(MOD(RIGHT(B2,2)-RIGHT(A2,2),24),"\:00")

Hi,

I'm currently working on a spreadsheet which has 7000 rows (source tab)

On the other tabs, I've got formulas picking up certain data...but my goodness, when I have to add a line or delete or save...it takes ages to process. Is there any way I can speed things up?

The file size is 1,611 KB

Thanks
Jen