Simple Calculation

Hi,

becouse I newbie this question might sound little stupid

In VBA I created UserForm where I inserted three TextBoxes:
- TextSkupajDDV
- TextBrezDDV
- TextSkupajZDDV

I created formula that would check if the statemant is right:

Private Function IzracunDDV()

If (TextSkupajDDV + TextBrezDDV= TextSkupajZDDV) Then
IzracunDDV = False
Exit Function
End If

Application.ScreenUpdating = True
IzracunDDV = True
End Function

Can you please tell me what I did wrong, becouse it is not currect

Hi,

what are you trying to test?

In your code you basically are saying this:

If textbox1 + textbox 2 = textbox1 then ...

this test will only return true if either textbox 1 or textbox 2 are empty
(of it they're numeric, equal to zero)

are you trying to see if one is empty?

HTH

Philip

"Lucifix" wrote:

>
> Hi,
>
> becouse I newbie this question might sound little stupid
>
> In VBA I created UserForm where I inserted three TextBoxes:
> - TextSkupajDDV
> - TextBrezDDV
> - TextSkupajZDDV
>
> I created formula that would check if the statemant is right:
>
> > Private Function IzracunDDV()
> >
> > If (TextSkupajDDV + TextBrezDDV= TextSkupajZDDV) Then
> > IzracunDDV = False
> > Exit Function
> > End If
> >
> > Application.ScreenUpdating = True
> > IzracunDDV = True
> > End Function
>
> Can you please tell me what I did wrong, becouse it is not currect
>
>
> --
> Lucifix
> ------------------------------------------------------------------------
> Lucifix's Profile: http://www.excelforum.com/member.php...o&userid=29179
> View this thread: http://www.excelforum.com/showthread...hreadid=495452
>
>

I would like check if formula is correct:
If textbox1 + textbox 2 = textbox1 then ...
If 2 + 1 = 3 then...

Thank you.

Hi,

Well, then you need code like this:

if textbox1 + textbox2 = textbox3 then ...

NOT

if textbox1+textbox2=textbox1

you were testing the values of 2 textboxes againts one of them - this will
NEVER be correct unless one of the textboxes is empty or =0 !

HTH

Philip

"Lucifix" wrote:

>
> I would like check if formula is correct:
> If textbox1 + textbox 2 = textbox1 then ...
> If 2 + 1 = 3 then...
>
> Thank you.
>
>
> --
> Lucifix
> ------------------------------------------------------------------------
> Lucifix's Profile: http://www.excelforum.com/member.php...o&userid=29179
> View this thread: http://www.excelforum.com/showthread...hreadid=495452
>
>

But what is different then this:

Private Function IzracunDDV() As Boolean

If TextSkupajDDV + TextBrezDDV = TextSkupajZDDV Then
IzracunDDV = False
Exit Function
End If

Application.ScreenUpdating = True
IzracunDDV = True
End Function

Lets say that I would like to insert in fields:
TextSkupajDDV -> 2
TextBrezDDV -> 1
TextSkupajZDDV -> 3

This function will say that this is not correct, which isn't true.
2 + 1 = 3

What would be right function then?

Thank you for helping me.
Lucifix

Try
CODE >>>
Private Function IzracunDDV() As Boolean

If VBA.CInt(TextSkupajDDV.Value) _
+ VBA.CInt(TextBrezDDV.Value) = VBA.CInt(TextSkupajZDDV.Value) Then
IzracunDDV = True
Exit Function
End If

Application.ScreenUpdating = True
IzracunDDV = False
End Function
<<<< END CODE

to check that 3 numbers are the same you need to force them to be numbers.

The default property of a textbox is the Text property, so in fact, if you
don't get the Value property explicitly, then the test you are performing is
like this:

text1 + text2 = "12"

is "12" = "3"

answer, FALSE

But if you take the textbox value property (which is still a string BTW)
then cast it to an integer using CINT then you get this test:

1 +3 = 3

One more thing, you are returning "IzracunDDV = False" if the condition is
found to be true - is that correct?

HTH

Philip
"Lucifix" wrote:

>
> But what is different then this:
>
> Private Function IzracunDDV() As Boolean
>
> If TextSkupajDDV + TextBrezDDV = TextSkupajZDDV Then
> IzracunDDV = False
> Exit Function
> End If
>
> Application.ScreenUpdating = True
> IzracunDDV = True
> End Function
>
> Lets say that I would like to insert in fields:
> TextSkupajDDV -> 2
> TextBrezDDV -> 1
> TextSkupajZDDV -> 3
>
> This function will say that this is not correct, which isn't true.
> 2 + 1 = 3
>
> What would be right function then?
>
> Thank you for helping me.
> Lucifix
>
>
> --
> Lucifix
> ------------------------------------------------------------------------
> Lucifix's Profile: http://www.excelforum.com/member.php...o&userid=29179
> View this thread: http://www.excelforum.com/showthread...hreadid=495452
>
>

Thank you very much, your function is working!

@One more thing, you are returning "IzracunDDV = False" if the condition is
found to be true - is that correct?

On the top I made code that would show you Msgbox if (IzracunDDV = True). Yeah I know that I could mix it, but this is how I made

Thank you again

Now I recived error when I tried to enter 6 digit number. I recived this message:

Run-time error '6':

Overflow

Hi,

this is because an integer has a maximum value of 32000 so when the code
tries to execute vba.cint(text.value) there is an overflow of the memory
allocated for that variable.

to use larger numbers, depending on the desired precision, you would use a
Single, a Double, or a Long

so instead ogf using cInt(...) you need to use one of these:

vba.clng(...)
vba.CDbl(...)
vba.CSng(..)

HTH

Philip

"Lucifix" wrote:

>
> Now I recived error when I tried to enter 6 digit number. I recived this
> message:
>
> Run-time error '6':
>
> Overflow
>
>
>
> --
> Lucifix
> ------------------------------------------------------------------------
> Lucifix's Profile: http://www.excelforum.com/member.php...o&userid=29179
> View this thread: http://www.excelforum.com/showthread...hreadid=495452
>
>

All seems a little overengineered.
what about...

Private Function IzracunDDV()
IzracunDDV = True

If (TextSkupajDDV + TextBrezDDV= TextSkupajZDDV) Then
IzracunDDV = False
End If

End Function

Hi,

That's because TextSkupajDDV + TextBrezDDV = string AND string, not number
plus number.

By default, VBA concatenates strings, and the default property of a textbox
is the text property, which is a string data type.

so, if you enter 1 and 2 in TextSkupajDDV and TextBrezDDV, and 3 in
TextSkupajZDDV then your test will be

is "12" = "3"

wheras, if you take the value of the testbox, and cast it to a numeric
value, then you can force an addition, rather than a string concatenation.

....though I agree there's probably a simpler way to do it, but I was
following the 'teach a man to fish...' paradigm rather than the 'give the man
a fish' ...



Philip

"MattShoreson" wrote:

>
> All seems a little overengineered.
> what about...
>
> Private Function IzracunDDV()
> IzracunDDV = True
>
> If (TextSkupajDDV + TextBrezDDV= TextSkupajZDDV) Then
> IzracunDDV = False
> End If
>
> End Function
>
>
> --
> MattShoreson
> ------------------------------------------------------------------------
> MattShoreson's Profile: http://www.excelforum.com/member.php...fo&userid=3472
> View this thread: http://www.excelforum.com/showthread...hreadid=495452
>
>

Philips foruma works just well, but I get error if I leave all fields empty. Do you have any idea to fix this?

Run-time error '13'.

Type mismatch

Hi,

This is because in order to use a function like CLng or CInt or CSng the
value you pass into the function must be numeric.

If you leave any of the textboxes blank then this throws an error as the
function (CSng etc) cannot convert an empty string into a numeric value.

So you need to check the values of the textboxes before you try to check them.

You can use code like:

if (vba.len(textbox1.value) >0 and vba.len(textbox2.value) >0 and
vba.len(textbox3.value) >0) then
If (vba.isnumeric(textbox1.value) and vba.isnumeric(textbox1.value) and
vba.isnumeric(textbox1.value) ) then
' ALL OK, so check if values ad up
else
' not numeric - is it a text value
msgbox "please enter numbers only in the fields",
vbexclamation+vbokonly
end if
else
msgbox "Please fill each field", vbexclamation+vbokonly
end if

HTH

Philip
"Lucifix" wrote:

>
> Philips foruma works just well, but I get error if I leave all fields
> empty. Do you have any idea to fix this?
>
> Run-time error '13'.
>
> Type mismatch
>
>
> --
> Lucifix
> ------------------------------------------------------------------------
> Lucifix's Profile: http://www.excelforum.com/member.php...o&userid=29179
> View this thread: http://www.excelforum.com/showthread...hreadid=495452
>
>