VBA Tips - Pause Code

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

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.

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

I have a VBA Code that works with both Excel and Internet Explorer. I have a little bit of code running to pull up IE, open a few windows and then search for an evaluation. When the search takes place, the eval I am trying to look up is 'PB E2E', but there are 8 evaluations with that string and it pulls the first one on the list which is 'H1 PB E2E'. I need the Search screen to pause/wait and then allow the user to select the correct Evaluation and then continue on with the rest of the code.

Thanks in advance.

Hi Friends,

I hope you are all ready for the New Year. I have close code that runs after a macro runs. The problem I am having is the Macro sends an email which seems it can take up to 60 seconds, and the close code is trying to run before the macro has completed. I was wondering if there is any way to tell the code below to wait 60 seconds before trying to quit?

Any ideas? Thanks so much!

Code:

Public Function closemyform()
DoCmd.Quit
End Function

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

How can I stop a macro at a cell so I can input a number in the cell before moving ahead?

Is it possible to pause the VBA code to give Excel time to calculate before proceeding? What's the VBA statement?

I read about a wait time function but that pauses the entire application and would not allow for calculations.

Thanks,

D

maybe

application.ENABLEEVENTS
Range("B2").QueryTable.Refresh BackgroundQuery:=False
ActiveWorkbook.Save
Application.Quit


--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Luke Bailey" <LukeBailey@discussions.microsoft.com> wrote in message
news:5096B3C7-2884-4A8B-BD90-8718CA70EC2E@microsoft.com...
>I have a workbook with an ON-OPEN VB sub that runs which refreshes a web
> query, then saves, then closes. The code is below:
> >
> I am not having problems with the workbook, but some of my users in other
> locations with similar workbook are having problems (workbook opens and
> saves, but seems to do so before the web query refreshes. Is there
> anything
> else other than DoEvents that can be used to pause code execution?

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.