Running VBA in excel is too slow, how to make it faster?

**rsbuslon** · 03-14-2013, 03:38 AM

Hi I'm not an expert in VBA and is just trying to 'crawl' my way in finishing a project. I have a code for my command button (Button_Submit) and the code is below. The results are too slow before it come up. Can I make it faster? Please help.

Private Sub Button_Submit_Click()
Dim iRow As Long
Dim ws As Worksheet

Set ws = Worksheets("INFLOWS_WC")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a Date
If Trim(Me.textboxDate.Value) = "" Then
    Me.textboxDate.SetFocus
    MsgBox "Please complete the form!", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
Exit Sub
End If

'check for Assistant name
If Trim(Me.textboxAssistant.Value) = "" Then
    Me.textboxAssistant.SetFocus
    MsgBox "Assistant field should never be blank!", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
Exit Sub
End If

'check for Term of payment value
If Me.comboTerm.Value = "" Then
    Me.comboTerm.SetFocus
    MsgBox "Term of payment should never be blank!", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
Exit Sub
End If

'check for Amount
If Trim(Me.textboxAmount.Value) = "" Then
    Me.textboxAmount.SetFocus
    MsgBox "Please indicate amount!", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
Exit Sub
End If

'Check if Card is payment type
Select Case Me.comboTerm.Value
    Case "Card"
        Select Case Me.textboxBatch.Value
            Case Is = ""
                Me.textboxBatch.SetFocus
                MsgBox "Batch No. cannot be empty for Card payment type.", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
            Case Else
                Select Case Me.comboDoctor.Value
                    Case Is = ""
                                Me.comboDoctor.SetFocus
                                MsgBox "Please select a Doctor.", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
                    Case Is = "WhatClinic", "Others"
                        Set ws = Worksheets("INFLOWS_WC")
                        iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
                        ws.Cells(iRow, 1).Value = Me.textboxDate.Value
                        ws.Cells(iRow, 2).Value = Me.textboxFirstname.Value
                        ws.Cells(iRow, 3).Value = Me.textboxLastname.Value
                        ws.Cells(iRow, 4).Value = Me.textboxTreatment.Value
                        ws.Cells(iRow, 5).Value = Me.comboTerm.Value
                        ws.Cells(iRow, 6).Value = Me.textboxBatch.Value
                        ws.Cells(iRow, 8).Value = Me.textboxAmount.Value
                        ws.Cells(iRow, 9).Value = Me.comboDoctor.Value
                        ws.Cells(iRow, 11).Value = Me.textboxAssistant.Value

                        MsgBox "Entry added to Inflows (WhatClinic) database.", vbOKOnly + vbInformation, "Data Added"

                        Me.textboxDate.Value = ""
                        Me.textboxFirstname.Value = ""
                        Me.textboxLastname.Value = ""
                        Me.textboxTreatment.Value = ""
                        Me.comboTerm.Value = ""
                        Me.textboxBatch.Value = ""
                        Me.textboxAmount.Value = ""
                        Me.comboDoctor.Value = ""
                        Me.textboxAssistant.Value = ""
                        Me.textboxDate.SetFocus
                    Case Else
                        Set ws = Worksheets("INFLOWS_PERSONAL")
                        iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
                        ws.Cells(iRow, 1).Value = Me.textboxDate.Value
                        ws.Cells(iRow, 2).Value = Me.textboxFirstname.Value
                        ws.Cells(iRow, 3).Value = Me.textboxLastname.Value
                        ws.Cells(iRow, 4).Value = Me.textboxTreatment.Value
                        ws.Cells(iRow, 5).Value = Me.comboTerm.Value
                        ws.Cells(iRow, 6).Value = Me.textboxBatch.Value
                        ws.Cells(iRow, 8).Value = Me.textboxAmount.Value
                        ws.Cells(iRow, 9).Value = Me.comboDoctor.Value
                        ws.Cells(iRow, 11).Value = Me.textboxAssistant.Value

                        MsgBox "Entry added to Inflows (Personal) database.", vbOKOnly + vbInformation, "Data Added"

                        Me.textboxDate.Value = ""
                        Me.textboxFirstname.Value = ""
                        Me.textboxLastname.Value = ""
                        Me.textboxTreatment.Value = ""
                        Me.comboTerm.Value = ""
                        Me.textboxBatch.Value = ""
                        Me.textboxAmount.Value = ""
                        Me.comboDoctor.Value = ""
                        Me.textboxAssistant.Value = ""
                        Me.textboxDate.SetFocus
                End Select
            End Select
    Case "Check"
        Select Case Me.textboxBatch.Value
            Case Is = ""
                Me.textboxBatch.SetFocus
                MsgBox "Batch No. cannot be empty for Check payment type.", vbOKOnly + vbCritical + vbDefaultButton1, "Alert!"
            Case Else
                Set ws = Worksheets("INFLOWS_WC")
                        iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
                        ws.Cells(iRow, 1).Value = Me.textboxDate.Value
                        ws.Cells(iRow, 2).Value = Me.textboxFirstname.Value
                ws.Cells(iRow, 3).Value = Me.textboxLastname.Value
                ws.Cells(iRow, 4).Value = Me.textboxTreatment.Value
                ws.Cells(iRow, 5).Value = Me.comboTerm.Value
                ws.Cells(iRow, 6).Value = Me.textboxBatch.Value
                ws.Cells(iRow, 8).Value = Me.textboxAmount.Value
                    ws.Cells(iRow, 9).Value = Me.comboDoctor.Value
                    ws.Cells(iRow, 11).Value = Me.textboxAssistant.Value

                    MsgBox "Entry added to Inflows (WhatClinic) database.", vbOKOnly + vbInformation, "Data Added"

                Me.textboxDate.Value = ""
                Me.textboxFirstname.Value = ""
                Me.textboxLastname.Value = ""
                Me.textboxTreatment.Value = ""
                Me.comboTerm.Value = ""
                Me.textboxBatch.Value = ""
                Me.textboxAmount.Value = ""
                Me.comboDoctor.Value = ""
                Me.textboxAssistant.Value = ""
                Me.textboxDate.SetFocus
        End Select
    Case Else
        Set ws = Worksheets("INFLOWS_WC")
                iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
                ws.Cells(iRow, 1).Value = Me.textboxDate.Value
                ws.Cells(iRow, 2).Value = Me.textboxFirstname.Value
                ws.Cells(iRow, 3).Value = Me.textboxLastname.Value
                ws.Cells(iRow, 4).Value = Me.textboxTreatment.Value
                ws.Cells(iRow, 5).Value = Me.comboTerm.Value
                ws.Cells(iRow, 6).Value = Me.textboxBatch.Value
                ws.Cells(iRow, 8).Value = Me.textboxAmount.Value
                ws.Cells(iRow, 9).Value = Me.comboDoctor.Value
                ws.Cells(iRow, 11).Value = Me.textboxAssistant.Value

                MsgBox "Entry added to Inflows (WhatClinic) database.", vbOKOnly + vbInformation, "Data Added"

                Me.textboxDate.Value = ""
                Me.textboxFirstname.Value = ""
                Me.textboxLastname.Value = ""
                Me.textboxTreatment.Value = ""
                Me.comboTerm.Value = ""
                Me.textboxBatch.Value = ""
                Me.textboxAmount.Value = ""
                Me.comboDoctor.Value = ""
                Me.textboxAssistant.Value = ""
                Me.textboxDate.SetFocus
End Select
End Sub

**OllieB** · 03-14-2013, 03:58 AM

Replace all occurrences of

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

by

iRow = ws.Cells(ws.Rows.Count,"A").End(xlup).Row+1

at the beginning of your routine you execute the following code without any purpose that I can identify - i.e. can be removed

Set ws = Worksheets("INFLOWS_WC")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

**rsbuslon** · 03-14-2013, 05:18 AM

at the beginning of your routine you execute the following code without any purpose that I can identify - i.e. can be removed

i have two sheets that corresponds to the submit button, namely INFLOWS_WC and INFLOWS_PERSONAL, so basically i have records to input and the conditions above would determine to which sheet the records will be added.

iRow = ws.Cells(ws.Rows.Count,"A").End(xlup).Row+1

However, i tried this, and it made the execution so fast! This is what I need. Thanks for the help!

**OllieB** · 03-14-2013, 05:55 AM

Originally Posted by rsbuslon

i have two sheets that corresponds to the submit button, namely INFLOWS_WC and INFLOWS_PERSONAL, so basically i have records to input and the conditions above would determine to which sheet the records will be added.

I know, but within the Select case statement you again execute the code to determine the first available row, so the first one at the top of the routine does not seem to be needed