Help with a simple VBA

[Kakzie]

Hi all,

I am a complete beginner so please bear with me
Ive just started computer science and am stuck with one of my assignments
The exercise I struggle with is below.
I assume I must ask the user for the different values and make an "IF statement" for the last part calculating the bonus according to the number of hours worked but I don't know how to put the equation for the number of hours (overtime - (2/3)*Absent) into the macro.

Exercise:
Write VBA macro to read a employee name (NAME), overtime hours worked (OVERTIME), hours absent (ABSENT) and determine bonus payment (PAYMENT).

Use the following table to calculate bonus payment

Bonus Schedule
OVERTIME – (2/3)*ABSENT	Bonus Paid
>40 hours >30 but £ 40 hours >20 but £ 30 hours >10 but £ 20 hours £ 10 hours	$50 $40 $30 $20 $10 please, help! many thanks in advance

[SamT]

You can use InputBoxes in the Code to get the information, but it is a clunky user interface.

You can use Cells on the Worksheet for user input and trigger the code when all input cells are not empty. See the Worksheet Change Event. or you can insert a CommandButton Control in the worksheet that the User can Click.

To get an A+, you can use a VBA UserForm with UserForm Controls to get the input, trigger the calculating code, and display the results. IMO, this is the most User Friendly and professional way.

If X = BonusHours

X = OTHours - 2 * AbsentHours / 3

Then you could use a Select Case to select the Bonus Value and display it in a MsgBox.

Note that a Select Case is similar to an If... Then... IfElse... Statement in that when one condition is met, none of the others are checked, so
>40
>30
>0
Will only return one result

OR, you could use a User Defined Function to return the Bonus Value

Bonus = GetBonus(BonusHours)

Please note that while we do help students find a solution to their homework, we do not write any code for home work assignments. Yeah, I cheated with the formula for X, but that was a specific question

[Kakzie]

Hi again !

So Here is the macro I have created:

Dim NAME As String
Dim OVERTIME As Integer
Dim ABSENT As Integer
Dim X As Integer
Dim Bonus As Integer
NAME = InputBox("Enter name")
OVERTIME = InputBox("Enter hours of overtime")
ABSENT = InputBox("Enter hours of absenteism")
X = OVERTIME - 2 * ABSENT / 3
If X > 40 Then Bonus = 50
If X > 30 And X <= 40 Then Bonus = 40
If X > 20 And X <= 30 Then Bonus = 30
If X > 10 And X <= 20 Then Bonus = 20
If X <= 10 Then Bonus = 10
MsgBox ("Bonus" & Bonus)

It works so I'm content (it may seem trivial and flawed to you but as I said I only just began so I only know the very basic stuff) anyway I was wondering if you could see anything really wrong with it, or something I could obviously improve about it?
For instance, should some of the data be introduced As Double (Dim ... As Double)?
Many thanks again

[SamT]

I would give you a D, but I love VBA

Using NAME is really wrong because Name is a reserved Keyword, You can use EmpName, (employee name,) instead. You can test for Keywords by placing the typing Cursor in the word and pressing F1 as if you were bringing up the help file for the Word, if it's not a keyword, there will be no help for it. Fix that for a C

Integers are good for numbers up to 32K, so you cannot use them for counting Row and many other things. Microsoft is talking about deprecating Integers and just using Longs. Integers were needed when RAM was scarce and expensive. Doubles are for Decimal Numbers, but VBA is very forgiving and most time will translate the Right hand Value to the Left hand value without complaint. (Integer = Double = no problem except you lose the unused, (for now?) Decimal fraction.) If the parameters for the bonus change to fractional Hours, you must refactor the entire procedure. Did I mention that I love VBA?

InputBoxes return Strings, but you're comparing numbers. Fortunately, VBA is very forgiving. Check out the Help on CInt, CLng, and CDbl.

MsgBoxes only accept Strings, fortunately the & "function" incorporates a CStr method. BTW, haveyou reallylooked atthe MsgBoxoutput?

That kludgey string of 'If... And ... Then's is keeping you down to a B. If you want an A, use 'If...Then... ElseIf... ElseIf...ElseIf... Else', (or a Select Case,) and no 'And's.


"Give me an AYE!"
"Give me an AYE!"
"Give me an AYE!"

YAY TEAM!

[Kakzie]

Hi

Alright, I think I understood about 65% of your reply which I find to be a very decent score. I did make some corrections based on what I understood and I think I'm close to getting your fictitious A. However, I'm not there yet because I don't understand how I could get rid of the "And". Furthermore, and I don't understand why, if I finish of with a simple "Else" at the end of the long string of "If...ElseIf...ElseIf..." the VBA stops me right there because even though VBA is quite the forgiving program, it has its limits.
The part about my really looking at the MsgBox output also got lost on me, way too subtle. It seemed important, though.
Many thanks again, I really appreciate it!

[SamT]

"And": If X > 40. the Select Case will end and never see X > 30 And X <40. If X > 30, the Select Case will end and never see X >20 And X < 30

Furthermore, and I don't understand why, if I finish of with a simple "Else" at the end of the long string of "If...ElseIf...ElseIf..." the VBA stops me right there because even though VBA is quite the forgiving program, it has its limits.

Show me an example

Assuming that the bonus is 40
MsgBox ("Bonus" & Bonus) displays Bonus40

MsgBox "Bonus is " & Bonus displays Bonus is 40. Note the trailing space in "Bonus is "

When learning to code, it is important that you stop looking at Strings as actual words and look at them as objects that must be strung together as words or sentences.

For example I see MsgBox ("Bonus" & Bonus) as Object(MsgBox) and Object(String) and Method(&) and Object(Number Variable)

Do you want to share what you have now?

[Kakzie]

Sure!

Dim EmployeeName As String
Dim OVERTIME As Double
Dim ABSENT As Double
Dim X As Double
Dim Bonus As Integer
EmployeeName = InputBox("Enter name")
OVERTIME = InputBox("Enter hours of overtime")
ABSENT = InputBox("Enter hours of absenteism")
X = OVERTIME - 2 * ABSENT / 3
If X > 40 Then Bonus = 50
ElseIf X > 30 Then Bonus = 40
ElseIf X > 20 Then Bonus = 30
ElseIf X > 10 Then Bonus = 20
Else: Bonus = 10
MsgBox ("Bonus Payment is $" & Bonus)

Where did I go wrong ?

[SamT]

B+.
Now incorporate the Employee's name in the Output for an A. "The Bonus for John Doe is $??" or "John Doe's Bonus is $??"

But, what you have now satisfies all the Assignment Requirements as you gave them in your first post. Good Work.

[Kakzie]

The B+ seems a bit much since it doesn't work... Apparently, there's a "compile error: Else without If" ?

I also tried to add "End If" towards the end and it complained that "End If without block If"

Why doesn't it see the If statement?

[SamT]

I missed that. My Bad. Try this

If X > 40 Then 
Bonus = 50 
ElseIf X > 30 Then 
Bonus = 40 
ElseIf X > 20 Then 
Bonus = 30 
ElseIf X > 10 Then 
Bonus = 20 
Else: Bonus = 10 
End If

Or this, Although I don't like the Colon = Next Line format

If X > 40 Then: Bonus = 50 
ElseIf X > 30 Then: Bonus = 40 
ElseIf X > 20 Then: Bonus = 30 
ElseIf X > 10 Then: Bonus = 20 
Else: Bonus = 10 
End If

but this is better

If X > 40 Then 
Bonus = 50 
ElseIf X > 30 Then 
Bonus = 40 
ElseIf X > 20 Then 
Bonus = 30 
ElseIf X > 10 Then 
Bonus = 20 
ElseIf X > 0 Then 
Bonus = 10 
End If

[Kakzie]

Yay! It's a beauty. I feel like quite the accomplished padawan.

Dim EmployeeName As String
Dim OVERTIME As Double
Dim ABSENT As Double
Dim X As Integer
Dim Bonus As Integer
EmployeeName = InputBox("Enter name")
OVERTIME = InputBox("Enter hours of overtime")
ABSENT = InputBox("Enter hours of absenteism")
X = OVERTIME - 2 * ABSENT / 3
If X > 40 Then
    Bonus = 50
ElseIf X > 30 Then
    Bonus = 40
ElseIf X > 20 Then
    Bonus = 30
ElseIf X > 10 Then
    Bonus = 20
Else: Bonus = 10
End If
MsgBox ("Bonus payment for " & EmployeeName & " is $" & Bonus)

OK I know this is probably like teaching a child how to count to 10 when you're an astrophysicist but since we're already here and all, I was wondering if you would mind taking a look at my other assignment? The macro works just fine but it could probably be improved.

"Write a VBA macro which adds up the numbers from 0 to n. ‘n’ is the input from the user."

Dim i As Integer, n As Integer
Dim ans As Integer
n = InputBox("Enter number")
ans = 0
i = 1
Do While i <= n
ans = ans + i
i = i + 1
Loop
MsgBox ("Result is" & ans)

[SamT]

One Final test of your "Bonus" code, Run the sub and input an Absent value greater than the Overtime value.
In the next assignment's code, give n a value of 32767 + 1 and run it.

See my post # 4 above

For a simple incrementing loop like that use

For i = 1 to n
'
'
'
Next i

You can format your code here by using the # Icon on the VBAExpress Message Editor menu.

• Copy your code from the VBA editor, Click the Icon and press Ctrl+V to paste between the CODE Tags.
• Type or paste the Code into the VBAExpress Editor, Select the code, then click the icon to put them around the selected Text.
• Manually type the Code Tags where they belong The Closing CODE Tag is "[/Code]" and the Opening Tag is "[Code]". They are not Case Sensitive. Put them in order and every thing between them is in a Block and nicely formatted as VBA Code with Indents and Colors

I edited your post to put CODE Tags around your code , but next time you will have to do it yourself

[Kakzie]

So, Long rather than Integer? Should I always use Long when it comes to user inputs, then (eg for the bonus code)?
And do you mean I should replace the Do While by For i to n like that:

For i = 1 To n
ans = ans + i
i = i + 1
Next i

[SamT]

In the VBA Editor, place the typing cursor in or next to a Key Word ( Integer| ) and press F1 to see the Help for that Word.


For i = x to n auto-increments with Next i


All the following are proper uses of For...To
For i = 1 to (X + Y) * Z
For i = 2 To 2^4 Step 2 (2^4 is Code for 2⁴)
For i = 19 To 1 Step -1
For i = LBound(MyArray) to UBound(MyArray)

[Kakzie]

I tried, I also tried selecting "Integer" and pressing F1 but it just opened the Help disregarding the word I wanted help with. Once inside the Help, searching the word Integer resulted useless too.

For i = x to n auto-increments with Next i

What does that imply?

[Kakzie]

I tried, I also tried selecting "Integer" and pressing F1 but it just opened the Help disregarding the word I wanted help with. Once inside the Help, searching the word Integer resulted useless too.

For i = x to n auto-increments with Next i

What does that imply?
Could I still use Do While or is it really out of place for such a basic loop? I'm not as comfortable with For (...) Next

(meant to edit last post of course -- can't seem to be able to delete either. Sorry for the mess)

[SamT]

For i = x to n auto-increments with Next i

For i = 1 to 10
X = i
Next i

The second time thru the loop, X and i equal 2, the third time thru, they = 3, the last time thru, they = 10

For i = 1 to 10 Step 3
X = i
Next '(i)

The first time thru, they = 1, the next time 4, the next time thru the loop, they =7, and finally 10

For - = 10 to 1 Step -1
X = i
Next

The first time, they = 10, the next, 9, then 8 and 7,6,5,4,3,2,1