UserForm calculator error

TessieBear99

New Member
Joined
Aug 26, 2018
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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.

1680579088368.png
1680579112751.png


This is my code:

VBA 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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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:

VBA Code:
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:

VBA Code:
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).
 
Upvote 0
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:

VBA Code:
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:

VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
The easiest way to solve it is just don't apply format to txtEntry before the calculation, therefore, delete or comment the section:

VBA Code:
Private Sub txtEntry_AfterUpdate()

You can apply the format after the calculation:

VBA Code:
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
 
Upvote 1
Solution
The easiest way to solve it is just don't apply format to txtEntry before the calculation, therefore, delete or comment the section:

VBA Code:
Private Sub txtEntry_AfterUpdate()

You can apply the format after the calculation:

VBA Code:
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 :)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,108
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top