Search code examples
excelvbauserform

Why is my if statement skewing my % increase in userform?

Have an if statement to calculate % increase/decrease within numbers inputted.

        If NewPayRate > 100 Then
            NewPayRate = NewPayRate / HrsPerYr
        Else
        End If

the purpose of this is if someone enters a $/hr figure and a new payrate is annualized, it would convert the new pay rate to an hourly figure and accurately show the % increase.

when I add the above IF statement, I get this:

enter image description here

The sections in yellow shouldn't be displaying that, it's clearly wrong. What am i missing? Rest of code:

Private Sub txtNewPayRate_Change()
    Const HrsPerYr As Long = 2080                      'Work Hours in Year
    Dim CurrentPayRate As Double, NewPayRate As Double
    Dim PercentChange As Double
    On Error Resume Next
    CurrentPayRate = CDbl(Me.txtCurrentPayRate)   'current hourly/annual pay
    NewPayRate = CDbl(Me.txtNewPayRate)           'new hourly/annual pay
        If NewPayRate > 100 Then
            NewPayRate = NewPayRate / HrsPerYr
        Else
        End If
        
    PercentChange = (NewPayRate - CurrentPayRate) / CurrentPayRate
    txtPercentage.Value = Format(PercentChange, "0.00%")

    If Me.cmbHourlyAnnual = "Hourly" Then
        Me.txtNewHourlyPay = Format(NewPayRate, "0.00")
        Me.txtNewAnnualPay = CStr(NewPayRate * HrsPerYr)

    ElseIf Me.cmbHourlyAnnual = "Annual" Then
        Me.txtNewHourlyPay = Format(NewPayRate / HrsPerYr, "0.00")
        Me.txtNewAnnualPay = CStr(NewPayRate)
    End If
End Sub
Solution

  • I would convert everything to hourly and go from there

    Private Sub txtNewPayRate_Change()
    
    Dim rawCurrentPayRate As Double
    Dim hrlyCurrentPayRate As Double
    Dim hrlyNewPayRate As Double
    
    Const HrsPerYr As Long = 2080                      'Work Hours in Year
    
    rawCurrentPayRate = CDbl(Me.txtCurrentPayRate.Value)

    If CDbl(Me.txtNewPayRate.Value) < 100 Then
        hrlyNewPayRate = CDbl(Me.txtNewPayRate.Value)
    Else
        hrlyNewPayRate = CDbl(Me.txtNewPayRate.Value) / HrsPerYr
    End If

    If Me.cmbHourlyAnnual = "Hourly" Then
        hrlyCurrentPayRate = rawCurrentPayRate
    Else
        hrlyCurrentPayRate = rawCurrentPayRate / HrsPerYr
    End If
    
    Me.txtNewHourlyPay = Format(hrlyNewPayRate, "0.00")
    Me.txtNewAnnualPay = Format(hrlyNewPayRate * HrsPerYr, "#,##0.00")
    Me.txtPercentage = Format((hrlyNewPayRate - hrlyCurrentPayRate) / hrlyCurrentPayRate, "0.00%")
    
End Sub