Consulting

Results 1 to 17 of 17

Thread: Help with a simple VBA

  1. #1
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location

    Help with a simple VBA

    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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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
    Last edited by SamT; 12-02-2015 at 04:50 PM.

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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!
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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!

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    "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?
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  7. #7
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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 ?
    Last edited by SamT; 12-03-2015 at 02:49 PM.

  8. #8
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  9. #9
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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?
    Last edited by Kakzie; 12-03-2015 at 04:45 PM.

  10. #10
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  11. #11
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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)
    Last edited by SamT; 12-03-2015 at 10:03 PM.

  12. #12
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  13. #13
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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
    Last edited by Kakzie; 12-04-2015 at 06:16 AM.

  14. #14
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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 24)
    For i = 19 To 1 Step -1
    For i = LBound(MyArray) to UBound(MyArray)
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  15. #15
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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?

  16. #16
    VBAX Regular
    Joined
    Nov 2015
    Posts
    9
    Location
    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)

  17. #17
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,101
    Location
    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
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •