UserForm calculator error

[TessieBear99]

Hi all,

I've created a UserForm calculator to remove the tax component from a value (I've posted about this once previously) but I've just struck an issue - it's working perfectly in calculating numbers based off a value up to and including 999.99, but anything 1000 and over it's not working.

The calculation is finding x in "x+10%=y" with y being the number I enter in the first box. So in the first picture below you can see that the second box plus 10% equals the first box, however in the second picture it's clearly wrong.

This is my code:

Option Explicit

Private Sub cmdClear_Click()
' Clicking the Clear button resets the text boxes

txtCalc = ""
txtEntry = ""

End Sub

Private Sub cmdCalc_Click()
' Clicking the = button completes the calculation based off text box 1 (txtEntry) and returns the value in text box 2 (txtCalc)

Dim total As Currency
Dim GST As Double

GST = Val(txtEntry.Value) / 11
total = GST * 10
txtCalc = total

    
End Sub

Private Sub cmdClose_Click()
' Clicking the Close button closes the window

Unload Me

End Sub

Private Sub cmdCopy_Click()
' Clicking the Copy button copies the value in text box 2 (txtCalc) to the clipboard

   With Me.txtCalc
      .SelStart = 0
      .SelLength = Len(.Text)
      .Copy
   End With
End Sub

Private Sub txtCalc_Change()
' Formats the value in text box 2 (txtCalc) once it has been populated

    Me.txtCalc = Format(Me.txtCalc, "#,##0.00##")

End Sub

Private Sub txtEntry_AfterUpdate()
'Formats the value in text box 1 (txtEntry) once it has been entered

    Me.txtEntry = Format(Me.txtEntry, "#,##0.00")

End Sub

Any assistance and an explanation would be greatly appreciated
Thanks
Tess

 

[Jimmypop]

Hi there...

Untested but maybe try...

Based on your code, it seems like you're trying to calculate the GST-exclusive value (i.e., the value before tax) from the GST-inclusive value (i.e., the value after tax) entered in txtEntry.

However, your current calculation assumes that the GST rate is fixed at 10%. In reality, the GST rate may vary depending on the country or region. In addition, it's important to note that the formula to calculate the GST-exclusive value from the GST-inclusive value is:

GST-exclusive value = GST-inclusive value / (1 + GST rate)

To incorporate this formula into your code, you can modify your cmdCalc_Click event handler as follows:

Private Sub cmdCalc_Click()
Dim total As Currency
Dim GST As Double
    
GST = Val(txtEntry.Value) / 11 'Assuming GST rate of 10%
    total = Val(txtEntry.Value) - GST
    
    txtCalc = total
End Sub

In this updated code, we first calculate the GST amount by dividing the GST-inclusive value by (1 + GST rate), where GST rate is assumed to be 10%. We then subtract this GST amount from the GST-inclusive value to obtain the GST-exclusive value. This value is then displayed in txtCalc.
Note that the Val function in VBA converts a string expression to a numeric value. Also, you may want to add some error handling code to handle cases where the user enters invalid input (e.g., non-numeric values).

 

[TessieBear99]

Hi there...

Untested but maybe try...

Based on your code, it seems like you're trying to calculate the GST-exclusive value (i.e., the value before tax) from the GST-inclusive value (i.e., the value after tax) entered in txtEntry.

However, your current calculation assumes that the GST rate is fixed at 10%. In reality, the GST rate may vary depending on the country or region. In addition, it's important to note that the formula to calculate the GST-exclusive value from the GST-inclusive value is:

GST-exclusive value = GST-inclusive value / (1 + GST rate)

To incorporate this formula into your code, you can modify your cmdCalc_Click event handler as follows:

Private Sub cmdCalc_Click()
Dim total As Currency
Dim GST As Double
   
GST = Val(txtEntry.Value) / 11 'Assuming GST rate of 10%
    total = Val(txtEntry.Value) - GST
   
    txtCalc = total
End Sub

In this updated code, we first calculate the GST amount by dividing the GST-inclusive value by (1 + GST rate), where GST rate is assumed to be 10%. We then subtract this GST amount from the GST-inclusive value to obtain the GST-exclusive value. This value is then displayed in txtCalc.
Note that the Val function in VBA converts a string expression to a numeric value. Also, you may want to add some error handling code to handle cases where the user enters invalid input (e.g., non-numeric values).

Thanks for your response. I'm in Australia and our GST is always 10% so that's why it's set at that. My error is still happening, it just seems that this does the exact same calculation but in a different way.

 

[Mario_R]

Hi.
The only symbol that the 'Val' function understands is "." but "," doesn't get it.

Thus:
- 1,000.00 is not understood, but
- 1000.00 is understood.

 

[Leo Skywalker]

The easiest way to solve it is just don't apply format to txtEntry before the calculation, therefore, delete or comment the section:

Private Sub txtEntry_AfterUpdate()

You can apply the format after the calculation:

Private Sub txtCalc_Change()
' Formats the value in text box 2 (txtCalc) once it has been populated

    Me.txtCalc = Format(Me.txtCalc, "#,##0.00##")
    Me.txtEntry = Format(Me.txtEntry, "#,##0.00##")

End Sub

 

[TessieBear99]

The easiest way to solve it is just don't apply format to txtEntry before the calculation, therefore, delete or comment the section:

Private Sub txtEntry_AfterUpdate()

You can apply the format after the calculation:

Private Sub txtCalc_Change()
' Formats the value in text box 2 (txtCalc) once it has been populated

    Me.txtCalc = Format(Me.txtCalc, "#,##0.00##")
    Me.txtEntry = Format(Me.txtEntry, "#,##0.00##")

End Sub

Brilliant, thank you! I knew I'd mucked up somewhere. Appreciate your help