CODEWITHSUNDEEP
vbaexcel
Maurice
Maurice

Reputation: 57

Unload my user form VBA

I made a function which should convert the gross income to net income.

In this case the gross income would be provided in the cell B1.

The function works as follow:

Start the function and select the input in the function so in this case its B1: =GrossNet(B1)

After that a user form should pop up which asks the user the pay frequency. (eg. Monthly, four weekly, weekly or daily.) After that has been selected the user form should unload which it does not...

If I change the function to sub it works... But when used as a function it does not, when I use it as function it wont unload the user form.

The code of the function is as follow: 

Public Function GrossNet(InputGross As Variant)
    Dim GrossPay As Variant
    Dim pay As String
    SelectPayFreq.Show
    pay = Sheets("xx").Range("AT50").Value
    Range("AT50").Cells.ClearContents
    GrossPay = InputGross

    'Checks whether the provided input is numerical.
    Dim CountGPay As Integer
    For CountGPay = 1 To Len(GrossPay)
        If Not IsNumeric(Mid(GrossPay, CountGPay, 1)) Then
            MsgBox ("The provided gross pay is not numeric!")
            Exit Function
        End If
    Next

    'Converts the pay basis to a monthly basis. eg weekly -> monthly, daily -> monthly etc.
    If pay = "Daily" Then
        GrossPay = GrossPay * 5 * 52 / 12
    ElseIf pay = "Weekly" Then
        GrossPay = GrossPay * 52 / 12
    ElseIf pay = "4Weeks" Then
        GrossPay = 52 * GrossPay / 48
    Else
    End If

    'Converts the provided gross pay to net pay with the use of the inverse of the lineair regression used @sub netgross.
    'R^2 = 0.9995 so the modal fits the data almost perfectly.
    Dim NetPay As Double
    If GrossPay <= 1633 Then
        NetPay = (GrossPay + 61.23) / 1.20326
    ElseIf 4180 >= GrossPay And GrossPay > 1633 Then
        NetPay = (GrossPay + 895.36) / 1.7958
    ElseIf 4800 >= GrossPay And GrossPay > 4180 Then
        NetPay = (GrossPay + 1278.6) / 1.9325
    ElseIf 8250 >= GrossPay And GrossPay > 4800 Then
        NetPay = (GrossPay + 2338.7) / 2.2694
    Else
        NetPay = (GrossPay + 1454.2) / 2.0833
    End If

    GrossNet = NetPay

End Function

the command in row 4: SelectPayFreq.Show loads the following userform with these functionalities: enter image description here

The code behind the user form is: 

Private Sub DailyBut_Click()
    Sheets("xx").Range("AT50").Value = "Daily"
    Unload Me
End Sub

Private Sub FWeekBut_Click()
    Sheets("xx").Range("AT50").Value = "4Weeks"
    Unload Me
End Sub

Private Sub MonthlyBut_Click()
    Sheets("xx").Range("AT50").Value = "Monthly"
    Unload Me
End Sub

Private Sub WeeklyBut_Click()
    Sheets("xx").Range("AT50").Value = "Weekly"
    Unload Me
End Sub

Could any be kind enough to help me out here?

Btw the reason I assign the value first in the cell AT50 is to transfer the variable from the userform to the function. Might there be a way to directly get the value without using the cell AT50?

Upvotes: 1

Views: 269

Answers (2)

mtholen
mtholen

Reputation: 1661

GD Maurice,

Please note that Me.Hide would hide your form...

But...

  1. You can save the pay variable straight to your function, without having to write it to the sheet.

Create a Module to which you copy your 'GrossNet' Function.

At the top of your new code module declare as follows:

Public pay as string

This will make the pay variable, accesible for all code, if you call it as Module1.pay (or whatever name you gave the module). You can then link the button_click directly to the pay variable, without having to write it to the sheet.

** edit ** You would have to remove the

Dim pay As String on line 2 of your function though, to avoid re-assigning (and hence clearing) the already declared pay variable.

I.e.

Private Sub WeeklyBut_Click()
    Module1.pay = "Weekly"
    Me.Hide     'this hides the form
    Unload Me   'this unloads it from memory
End Sub

  1. IsNumeric(<value>) is a function call for a variable, to check whether the variable -in its interity- is a numeric value. I.e. 

    If Not IsNumeric(Mid(GrossPay, CountGPay, 1)) Then
    MsgBox ("The provided gross pay is not numeric!")
    Exit Function            
End If

Is enough to check if it is a numeric value, no need for the For..to..loop

Try implementing some of the above tips, that should help you along a bit ?

Let me know if you have further questions ?

Cheers

Upvotes: 0

mielk
mielk

Reputation: 3940

You need to change two things in your code:

  • Frequency should be passed from the user form directly to the function (without writing it in the worksheet),

  • user form should be unloaded by the main function instead of unloading itself.

Here is your code modified as mentioned above:

[USER FORM CODE]

Option Explicit

Public Frequency As String

Private Sub DailyBut_Click()
    Frequency = "Daily"
    Call Me.Hide
End Sub

Private Sub FWeekBut_Click()
    Frequency = "4Weeks"
    Call Me.Hide
End Sub

Private Sub MonthlyBut_Click()
    Frequency = "Monthly"
    Call Me.Hide
End Sub

Private Sub WeeklyBut_Click()
    Frequency = "Weekly"
    Call Me.Hide
End Sub

[FUNCTION CODE]

Option Explicit

Public Function GrossNet(InputGross As Variant)
    Dim GrossPay As Variant
    Dim pay As String
    Dim freq As String

    With SelectPayFreq
        .Show
        freq = .Frequency
    End With
    Unload SelectPayFreq

    pay = Sheets("xx").Range("AT50").Value
    Range("AT50").Cells.ClearContents
    GrossPay = InputGross

    'Checks whether the provided input is numerical.
    Dim CountGPay As Integer
    For CountGPay = 1 To Len(GrossPay)
        If Not IsNumeric(Mid(GrossPay, CountGPay, 1)) Then
            MsgBox ("The provided gross pay is not numeric!")
            Exit Function
        End If
    Next

    'Converts the pay basis to a monthly basis. eg weekly -> monthly, daily -> monthly etc.
    If pay = "Daily" Then
        GrossPay = GrossPay * 5 * 52 / 12
    ElseIf pay = "Weekly" Then
        GrossPay = GrossPay * 52 / 12
    ElseIf pay = "4Weeks" Then
        GrossPay = 52 * GrossPay / 48
    Else
    End If

    'Converts the provided gross pay to net pay with the use of the inverse of the lineair regression used @sub netgross.
    'R^2 = 0.9995 so the modal fits the data almost perfectly.
    Dim NetPay As Double
    If GrossPay <= 1633 Then
        NetPay = (GrossPay + 61.23) / 1.20326
    ElseIf 4180 >= GrossPay And GrossPay > 1633 Then
        NetPay = (GrossPay + 895.36) / 1.7958
    ElseIf 4800 >= GrossPay And GrossPay > 4180 Then
        NetPay = (GrossPay + 1278.6) / 1.9325
    ElseIf 8250 >= GrossPay And GrossPay > 4800 Then
        NetPay = (GrossPay + 2338.7) / 2.2694
    Else
        NetPay = (GrossPay + 1454.2) / 2.0833
    End If

    GrossNet = NetPay

End Function

Upvotes: 1

Related Questions