Data input on excel: form sheet to database sheet

Question

I'm using a dynamic button on one sheet to send data to a database/summary sheet within the same workbook in Excel.

I know I know. Should've done this using Access and queries, but I used the trusty brute-force method in the code shown below.

It works, but it's painstakingly slow. Please advise on how to perform this task with less demand on the processor.

'enter into database
NextSPGP.Value = Range("B7").Value
NextDate.Value = Format(Range("M7").Value, "mm/dd/yyyy")
NextStart.Value = Format(Range("A12").Value, "hh:mm")
NextFinish.Value = Format(Range("B12").Value, "hh:mm")
NextMix = Range("C12").Text
NextBatch.Value = Range("D12").Value
NextGrouter.Value = Range("J7").Value
NextPump.Value = Range("H7").Value
NextPass.Value = Range("F7").Value
NextDepth.Value = Range("E12").Value
NextSleeve.Value = Range("F12").Value
NextInitPress.Value = Range("G12").Value
NextFinalPress.Value = Range("H12").Value
NextFlow.Value = Range("I12").Value
NextVol.Value = Range("J12").Value
NextMove.Value = Range("K12").Value
NextComment.Value = Range("L12").Value

Set `Application.Screenupdating = False` while running. Are you defining all those destination ranges in some other code? — Tim Williams, Aug 24 '16 at 00:24
Thanks for the idea. I always wanted to know how to stop that, but it wasn't updating the screen from sheet to sheet, so that didn't help the processing time. The destination ranges (i.e. NextSPGP) are defined ranges in the form: Set NextSPGP = LastGroutEntry.Offset(1, 0) — Clayton, Aug 24 '16 at 00:37
If all of those `NextXxxxx` ranges are on the same row then you can more-efficiently populate the values using an array, which you can place on the sheet in a single operation. — Tim Williams, Aug 24 '16 at 02:11

score 0 · Answer 1 · edited Feb 06 '21 at 01:31

I usually start of my routines with something like this:

Dim bScreenUpdating As Boolean      'Screen Updating Flag
    Dim bEnableEvents   As Boolean
    Dim lCalculation    As Long

    With Application
        bScreenUpdating = .ScreenUpdating
        bEnableEvents = .EnableEvents
        lCalculation = .Calculation
        .ScreenUpdating = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With

...and end them with something like this:

With Application
         .ScreenUpdating = bScreenUpdating
        .EnableEvents = bEnableEvents
        .Calculation = lCalculation
    End With

...so that you turn things off that might slow progress, and restore the users environment afterwards.

Also, unless you have a reason to use .value, use .value2. See Excel MVP and Recalculation Guru Charles Williams' respone to the following thread: What is the difference between .text, .value, and .value2?

Note that defining ranges in VBA code like you are doing is a recipe for disaster. As soon as someone inserts/deletes a row/column, all your VBA references are pointing at the wrong cells. I always give each of my ranges of interest a named range, and then reference that name in the VBA.

Also, you haven't given us much information about your workbook setup. How many values are we talking about here? Hundreds? Thousands? And is anything referencing the destination cells in the destination sheet? What else can you tell us about this workbook? i.e. is it a really big file with lots of formulas? What kinds of formulas? VLOOKUPS? OFFSET or other volatile functions? Are you writing these values into an Excel Table aka ListObject? These can really slow things down - particularly if you don't temporarily turn off calculation.

score 0 · Answer 2 · answered Aug 24 '16 at 02:15

Instead of this

NextSPGP.Value = Range("B7").Value
NextDate.Value = Format(Range("M7").Value, "mm/dd/yyyy")
NextStart.Value = Format(Range("A12").Value, "hh:mm")
NextFinish.Value = Format(Range("B12").Value, "hh:mm")
NextMix = Range("C12").Text
NextBatch.Value = Range("D12").Value
NextGrouter.Value = Range("J7").Value
'etc...

You can do something like:

Dim Arr
Arr = Array(Range("B7").Value, Format(Range("M7").Value, "mm/dd/yyyy"), _
            Format(Range("A12").Value, "hh:mm"),Format(Range("B12").Value, "hh:mm"), _
            Range("C12").Text, Range("D12").Value, Range("J7").Value)


NextSPGP.Resize(1,UBound(Arr)+1).Value = Arr 'populate row in one shot

Tim, really appreciate your help. I knew an array or a string function was the solution, but I couldn't find a solution anywhere online. Processing time is down to < 2 seconds. You're the man! — Clayton, Aug 25 '16 at 02:03

score 0 · Answer 3 · answered Aug 25 '16 at 02:12

Tim Williams' suggestion with some minor editing. The database row had two cells needing data manipulation of a time (date) variable and pressure differential of initial to final pressures on our grout pumping operation. Success below:

MsgBox "Starting data input..."
enter log data into database

Dim NewData
Dim Duration As Date
Dim PressureDiff As Long

Duration = Format((Range("B12") - Range("A12")), "hh:mm")
PressureDiff = (Range("H12") - Range("G12"))

NewData = Array(Range("B7").Value, Format(Range("M7").Value, "mm/dd/yyyy"), Format(Range("A12").Value, "hh:mm"), _
Format(Range("B12").Value, "hh:mm"), Duration, Range("C12").Text, Range("D12").Value, Range("J7").Value, _
Range("H7").Value, Range("F7").Value, Range("E12").Value, Range("F12").Value, Range("G12").Value, _
Range("H12").Value, PressureDiff, Range("I12").Value, Range("J12").Value, Range("K12").Value, Range("L12").Value)

NextSPGP.Resize(1, UBound(NewData) + 1).Value = NewData
MsgBox "Data input complete!"

Data input on excel: form sheet to database sheet

3 Answers3