Help in VBA code

[a_dani20]

Hi guys.. i am trying to create a sshort macro for calculation of incometax but it is showing " type mismatch error". Pls help. Also any suggestions to improve this macro are welcome...


Sub IT()

Dim Incometax As single

Dim ExemptionLimit As single


If Range("b6") = "Male" Then

ExemptionLimit = 190000

ElseIf Range("b6") = "Female" Then

ExemptionLimit = 225000

Else: ExemptionLimit = 250000

End If


If Range("b9") <= 300000 Then

Incometax = (Range("b9") - ExemptionLimit) * "10.3%"

ElseIf Range("b9") < "800000" Then

Incometax = Incometax + (Range("b9") - "300000") * "20.6%"

Else: Incometax = Incometax + (Range("b9") - "800000") * "30.9%"

End If

MsgBox (prompt: = _

Range("b5")&" your income tax is " & Incometax)

End Sub

 

[Jack]

Hi there a_dani20


I didn't spend long on this and got the mesgbox so should be OK. Just a few changes highlighted by 'X


Sub IT()

Dim Incometax As Single

Dim ExemptionLimit As Long

Dim sAnsw As String 'X see below

If Range("b6") = "Male" Then

ExemptionLimit = 190000

ElseIf Range("b6") = "Female" Then

ExemptionLimit = 225000

Else: ExemptionLimit = 250000

End If


If Range("b9") <= 300000 Then

Incometax = (Range("b9") - ExemptionLimit) * 0.103 'X if it's in quotes as VBA won't see as a number

ElseIf Range("b9") < "800000" Then

Incometax = Incometax + (Range("b9") - "300000") * 0.206 'X

Else: Incometax = Incometax + (Range("b9") - "800000") * 0.309 'X

End If

sAnsw = MsgBox(prompt:=Range("b5") & " your income tax is " & Incometax) 'X I have never really understood why but the msgbox seems to have to be assigned to a string variable - issue has sometimes driven me nuts - maybe someone else knows why

End Sub

 

[Jack]

Sorry didn't see the quotes also around other numbers eg "800000" remove all quotes.


Below again without any comments...


Sub IT()

Dim Incometax As Single

Dim ExemptionLimit As Long

Dim sAnsw As String

If Range("b6") = "Male" Then

ExemptionLimit = 190000

ElseIf Range("b6") = "Female" Then

ExemptionLimit = 225000

Else: ExemptionLimit = 250000

End If


If Range("b9") <= 300000 Then

Incometax = (Range("b9") - ExemptionLimit) * 0.103

ElseIf Range("b9") < 800000 Then

Incometax = Incometax + (Range("b9") - 300000) * 0.206

Else: Incometax = Incometax + (Range("b9") - 800000) * 0.309

End If

sAnsw = MsgBox(prompt:=Range("b5") & " your income tax is " & Incometax)

End Sub

 

[asa]

Hi,

Regarding using a variable with MsgBox--

MsgBox is a function, and functions are designed to return information when you call (use) them. The MsgBox function actually returns a number (a LONG, aka long integer).


If you don't specify any buttons for the message box other than the default OK, the return value will always, I think, be 1 or the constant vbOK, which equals 1, so it's true there's not much to be learned in that case...


You shouldn't have to assign the return value to a variable. The rule for calling a function when you want to discard the return value is to do away with the parenthesis, just as you would call a SUB procedure or an object's method, or use a VBA statement:

MsgBox "message"

[code]MsgBox Prompt:="Hi"

[code]MsgBox "Do you think a computer cares what you think?", buttons:=vbYesNo

If you only have one parameter to MsgBox and you don't name the parameter, it will appaear to allow you to use parenthesis:

MsgBox ("Hello, world.&#34[/code]


That will work. But it's misleading, because the parenthesis are not being interpreted as enclosing the parameter list, but as specifying the order of evaluation for string addition or concatenation (even though you're not doing that).


This will also work:

MsgBox ("Hello, world.&#34, (vbYesNo)[/code]

since they are serving the purpose of order of evaluation only. (in the second parameter it would be for math operations as vbYesNo is a numeric constant).


Asa

 

[Jack]

Many thanks asa, that explains why this has been a frustrating problem for me and it's those damn brackets!


a_dani20 here is your code reflecting asa's explanation.


I draw your attention to this line which you may have reason to do but I am curious - male of female elseif this?


Else: ExemptionLimit = 250000


Just seems like it might not be triggered.


Sub IT()

Dim Incometax As Single

Dim ExemptionLimit As Long

If Range("b6") = "Male" Then

ExemptionLimit = 190000

ElseIf Range("b6") = "Female" Then

ExemptionLimit = 225000

Else: ExemptionLimit = 250000

End If


If Range("b9") <= 300000 Then

Incometax = (Range("b9") - ExemptionLimit) * 0.103

ElseIf Range("b9") < 800000 Then

Incometax = Incometax + (Range("b9") - 300000) * 0.206

Else: Incometax = (Range("b9") - 800000) * 0.309

End If

MsgBox Range("b5") & " your income tax is " & Incometax

End Sub

 

[a_dani20]

thanks asa and John@ReddyBay.. but there is one problem guys macro is running but it is not producing desired result. actually the macro is to calulate income tax of a person depending on type - male, female or senior citizen ( that answers John question!!) and income level of person, but the macro is returning same answer for all type of persons. dont know where i am doing wrong. pls help..

 

[Jack]

@ a_dani20 I am glad you cleared that up on 3 x genders


It might be that B6 must a capitalised word eg Male not male?


Have you stepped through the macro using F8 with the locals window open? This will show the logic of your code and the best way to learn VBA (apart from siging up for Chandoo's VBA course!) In the VB Editor (Alt 11) go view locals window. Then place your cursor somewhere in the code and step though using F8. The locals window should show how your variables change one of which is tax and also how the code handles your if statements etc. It's going to be better if you solve this part of the exercise as you know what you want best in the logic of it.