PDA

View Full Version : [SOLVED] Simple VBA calculation



onmyway
02-03-2015, 04:14 AM
Hi all,

I have a simple math problem.

I am trying to automatically calculate the value of a couple fields.

It is a very simple equation:

txtStockEOS1 = txtStockSOS1.value + txtStockReceive1.value - txtStockLost1.value - txtStockBroken1.value - txtStockWorn1.value

I.e., 10 = 5+8-1-1-1

I get a run-time error 13: type mismatch when I enter a value in one of my fields whilst testing. This is my code:



Private Sub txtStockSOS1_Change()
Me.txtStockEOS1 = CDbl(txtStockSOS1.Value) + CDbl(txtStockReceive1.Value) - CDbl(txtStockLost1.Value) - CDbl(txtStockBroken1.Value) - CDbl(txtStockWorn1.Value)
End Sub

GTO
02-03-2015, 04:39 AM
CDbl() will error against an empty string, which is what the other textboxes return if empty when txtStockSOS1 is changed. Does that make sense?

Mark

onmyway
02-03-2015, 04:46 AM
Hi GTO

Thanks for the feedback.

It does make sense. What can I use for auto calculating then? i.e., _change event? Or how can i overcome the challenge of the blank fields?

Bob Phillips
02-03-2015, 06:05 AM
Private Sub txtStockSOS1_Change()
Dim tmp As Double
If txtStockSOS1.Value <> "" Then tmp = tmp + CDbl(txtStockSOS1.Value)
If txtStockReceive1.Value <> "" Then tmp = tmp + CDbl(txtStockReceive1.Value)
If txtStockLost1.Value <> "" Then tmp = tmp - CDbl(txtStockLost1.Value)
If txtStockBroken1.Value <> "" Then tmp = tmp - CDbl(txtStockBroken1.Value)
If txtStockWorn1.Value <> "" Then tmp = tmp - CDbl(txtStockWorn1.Value)
Me.txtStockEOS1 = tmp
End Sub

onmyway
02-03-2015, 06:27 AM
hi xld

Thank you very much! Works great!

One question: i need to have this _change event for each of the fields: txtStockSOS1; txtStockReceive1; txtStockLost1; txtStockBroken1; txtStockWorn1

is there an elegant way to create this event for all, or do I have to do it one-by-one for each field?

snb
02-03-2015, 06:59 AM
Private Sub txtStockSOS1_Change()
Me.txtStockEOS1 = val(txtStockSOS1.Value) + val(txtStockReceive1.Value) - val(txtStockLost1.Value) - val(txtStockBroken1.Value) - val(txtStockWorn1.Value)
End Sub

onmyway
02-03-2015, 07:03 AM
hi snb

thank you, works great! although xld's method worked as well, i got errors when i entered non numeric values (i have the fields pre-formatted to check for numeric values etc.).

Thank you all!

Paul_Hossler
02-03-2015, 07:18 AM
Don't know it's as elegant as XLD will come up with, but something like this with all the calculations captured in a sub in the same userform module would be the simplest approach (I think)

You still need the multiple _Change events

Also, I'd suggest adding some error checking to fail softly if a non-numeric entry happens to be made





Private Sub txtStockSOS1_Change()
If IsNumeric (txtStockSOS1.Value)
Call CalctxtStockEOS
Else
Msgbox "oops"
End If
End Sub

Private Sub txtStockSOS1_Change()


Call CalctxtStockEOS


End Sub


'etc.



Private Sub CalctxtStockEOS()
Dim tmp As Double
If txtStockSOS1.Value <> "" Then tmp = tmp + CDbl(txtStockSOS1.Value)
If txtStockReceive1.Value <> "" Then tmp = tmp + CDbl(txtStockReceive1.Value)
If txtStockLost1.Value <> "" Then tmp = tmp - CDbl(txtStockLost1.Value)
If txtStockBroken1.Value <> "" Then tmp = tmp - CDbl(txtStockBroken1.Value)
If txtStockWorn1.Value <> "" Then tmp = tmp - CDbl(txtStockWorn1.Value)
Me.txtStockEOS1 = tmp
End Sub

onmyway
02-03-2015, 07:23 AM
Hi Paul, thank you for the effort! I have decided to go with snb's method.

snb
02-03-2015, 07:29 AM
other approaches:


Private Sub UserForm_Click()
MsgBox Application.Sum(Array(Val(TextBox1), Val(TextBox2), Val(TextBox3), Val(TextBox4)))
End Sub


Private Sub UserForm_Click()
MsgBox Evaluate("sum(" & TextBox1 & "," & TextBox2 & "," & TextBox3 & "," & TextBox4 & ")")
End Sub

onmyway
02-03-2015, 07:39 AM
hi guys, thanks for your diligence! :)

Do you perhaps want to look at this challenge: Add multiple filtered row data to ListBox

:beg:

GTO
02-03-2015, 09:55 AM
hi guys, thanks for your diligence! :)

Do you perhaps want to look at this challenge: Add multiple filtered row data to ListBox

:beg:

If that was supposed to be a link, I think it failed.

Not well tested, but this seems to work, blocking non-numeric entries and using a Class instance for each of the five 'child' textboxes. Please test in a junk/throwaway copy of your workbook...

In the Userform's Module:

Option Explicit

'// You could use a collection, but since how many controls we have seems unlikely to be a //
'// mystery, I don't know anything against sizing an array. //
Private cAT(1 To 5) As clsAssociatedTextboxes

Private Sub UserForm_Initialize()
Dim ctrl As MSForms.Control
Dim n As Long

n = 0 '<--- just for clarity in my pea brain...
For Each ctrl In Me.Controls
Select Case ctrl.Name
Case "txtStockSOS1", "txtStockReceive1", "txtStockLost1", "txtStockBroken1", "txtStockWorn1"
n = n + 1
Set cAT(n) = New clsAssociatedTextboxes
Set cAT(n).Parent = Me
Set cAT(n).ThisTextbox = ctrl
Case Else
' do nothing
End Select
Next

End Sub

In a Class named clsAssociatedTextboxes:

Option Explicit

Private ParentForm As Object
Private WithEvents TB As MSForms.TextBox

Private LastPosition1 As Long

Public Property Set Parent(p As Object)
Set ParentForm = p
End Property
Public Property Get Parent() As Object
Set Parent = ParentForm
End Property
Public Property Set ThisTextbox(tt As MSForms.TextBox)
Set TB = tt
End Property
Public Property Get ThisTextbox() As MSForms.TextBox
Set ThisTextbox = TB
End Property

Private Sub TB_Change()
Static LastText As String
Static SecondTime As Boolean

' Credit to Rick Rothstein at:
' http://www.mrexcel.com/forum/excel-questions/831878-userform-check-textboxes-numerical-values.html
'// In gist - eliminate non-numerical values from being entered, allow a '+' or '-' sign at //
'// the start and allow one decimal point. //
With ThisTextbox
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or .Text Like "*.*.*" Or .Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition1
Else
LastText = .Text
LastPosition1 = .SelStart
End If
End If
End With
SecondTime = False

'// After disallowing non-numeric values, update the control. CDbl() appears necessary to //
'// force adding (vs. concatenation). //
Parent.Controls("txtStockEOS1").Value = _
CDbl(IIf(Not Parent.Controls("txtStockSOS1").Value = vbNullString, Parent.Controls("txtStockSOS1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockReceive1").Value = vbNullString, Parent.Controls("txtStockReceive1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockLost1").Value = vbNullString, Parent.Controls("txtStockLost1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockBroken1").Value = vbNullString, Parent.Controls("txtStockBroken1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockWorn1").Value = vbNullString, Parent.Controls("txtStockWorn1").Value, 0))

End Sub

Private Sub TB_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'// Record cursor position //
LastPosition1 = TB.SelStart
End Sub

Private Sub TB_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
'// Record cursor position //
LastPosition1 = TB.SelStart
End Sub

Hope that helps,

Mark

onmyway
02-03-2015, 10:18 AM
WOW, Mark. Thank you for the excellent response! A great big thumbs-up to you!

FYI: I tried a URL, but got an error: "Post denied. New posts are limited by number of URLs it may contain and checked if it doesn't contain forbidden words."

PS: I am still battling with that issue if you'd like to take a look.

Thank you for all your help!

GTO
02-03-2015, 10:20 AM
ACK!

Back to bed for an hour, so will check back later. I just realized that typing in the negative/positive will error. No doubt something better than this, but try changing the one part to:


Parent.Controls("txtStockEOS1").Value = _
CDbl(IIf(Not Parent.Controls("txtStockSOS1").Value = vbNullString And Not Parent.Controls("txtStockSOS1").Value = "-" And Not Parent.Controls("txtStockSOS1").Value = "+", Parent.Controls("txtStockSOS1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockReceive1").Value = vbNullString And Not Parent.Controls("txtStockReceive1").Value = "-" And Not Parent.Controls("txtStockReceive1").Value = "+", Parent.Controls("txtStockReceive1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockLost1").Value = vbNullString And Not Parent.Controls("txtStockLost1").Value = "-" And Not Parent.Controls("txtStockLost1").Value = "+", Parent.Controls("txtStockLost1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockBroken1").Value = vbNullString And Not Parent.Controls("txtStockBroken1").Value = "-" And Not Parent.Controls("txtStockBroken1").Value = "+", Parent.Controls("txtStockBroken1").Value, 0)) + _
CDbl(IIf(Not Parent.Controls("txtStockWorn1").Value = vbNullString And Not Parent.Controls("txtStockWorn1").Value = "-" And Not Parent.Controls("txtStockWorn1").Value = "+", Parent.Controls("txtStockWorn1").Value, 0))


Mark

onmyway
02-03-2015, 10:30 AM
noted:)

Paul_Hossler
02-03-2015, 10:43 AM
Hi Paul, thank you for the effort! I have decided to go with snb's method.


No problem ... less typing

1. You still need to add the calculation to the event handler for each textbox

2. Be aware that Val() will return 0 and not give you a message if the string you entered is not convertable