Simple VBA help needed

[Tom|Nbk]

hello all great forum, im pleased to be here, at the moment ive just finished studying VBA for my Applied ICT A level course we have been given a task

Program 1: Calculate the average monthly sales figure for a salesperson in one year.
A user will be asked to enter 12 sales figures (pounds and pence), one for each month of the year, and then the average monthly figure will be displayed.


Option Compare Database
Option Explicit
Dim NumStaff As Integer
Dim Marks As Integer
Dim Average As Single
Dim Total As Single
Dim count As Single
Dim Output As Single
Dim salesfigures As Single

Sub MainAvProg()
Call GetNumStaff
Call GetMarks
Call CalcAverage
Call DisplayAverage
End Sub

Sub GetNumStaff()
NumStaff = InputBox("Enter number of staff")
End Sub

Sub GetMarks()
Total = 0
Do
For count = 1 To 12
salesfigures = InputBox("Enter the sales figures")
Total = Total + salesfigures
Next count
Loop Until NumStaff
End Sub

Sub CalcAverage()
Average = NumStaff / Total
End Sub

Sub DisplayAverage()
MsgBox "The average is; " & Average
End Sub

this is my code so far but it is not complete if anyone can help me :help it would be greatly appreciated

theres a few others i need help with but i thought posting the first one would be best

Thanks,

Tom

[stanl]

Just a quick question. Do the people who will certify do so on your ability to get others to help you complete the task or on your ability to do so yourself? Stan

[Ken Puls]

Hi Tom, and welcome to VBAX!

Well, personally, I'd do things a little differently that what you have. I'm not sure of the scope of you project, but I don't see the need to break down your routines as far as you have. Even the following is a little overboard, possible, although I prefer to make my functions reusable:


Sub MainAvProg()
Dim NumStaff As Long
Dim Count As Long
Dim Total As Double
'Enter number of staff
NumStaff = InputBox("Enter number of staff")
'Get marks
Total = 0
For Count = 1 To 12
Total = Total + InputBox("Enter the sales figures")
Next Count
'Tell user average
MsgBox "The average is " & Format(CalcAverage(NumStaff, Total), "##.##")
End Sub

Function CalcAverage(ByVal NumStaff, ByVal Total) As Double
'Calculate average
CalcAverage = Total / 12
End Function


The changes I made were mostly to pull the farmed out routines back into the main one. Unless you need to call them from other routines, you shouldn't need to pass everything back and forth from a public variable.

I stripped the part where you asked for a count of the employees. If you have that, you'd get an average of ALL employees, not just the one.

I also divided your Total by 12 to get your average.

HTH,

[Ken Puls]

Just a quick question. Do the people who will certify do so on your ability to get others to help you complete the task or on your ability to do so yourself?

Hi Stan,

For reference, I answered this question because Tom did make a shot at doing it himself. Personally, as a professional, I believe that the knowledge of how to do things is important, but equally so is the ability to know where to find it if you don't have it.

I'm not going to answer all Tom's questions with solutions, but I don't see an issue of helping to teach him if he's made an effort to begin with.

Just my 2 cents. :)

[stanl]

I didn't ask the question to rag on the individual; it's just a lot of Boards have policies or at least concerns about assiting students with homework.

But hey - you be da professional - thanks for indicating what that makes me.

[Ken Puls]

I didn't ask the question to rag on the individual; it's just a lot of Boards have policies or at least concerns about assiting students with homework.

But hey - you be da professional - thanks for indicating what that makes me.

Hey Stan,

I was simply expressing my viewpoint on the homework issue. My personal standpoint is that as long as it is help, then the student will learn. If they just ask for an answer without doing any work, then yes, I find that offensive and will let the poster know.

To be clear, that was not a shot at you, or your professionalism, either, so please don't take it that way.

[Tom|Nbk]

Thanks for your help i did need to farm all them out to seperate sub procedures but with your help ive managed to do that *i hope* :rotlaugh: , as for my next program;

Program 2: Calculate the average monthly sales figure per salesperson, where the number of salespeople is variable.
For each salesperson the user will be able to enter the monthly sales figures (pounds and pence) for a year. At the end of entering the sales figures, the user will indicate if they would like the average calculated and displayed before continuing onto entering the sales figures for the next salesperson.

Option Explicit
Option Compare Database
Dim NumStaff As Integer
Dim Count As Integer
Dim Count2 As Integer
Dim Total As Integer
Dim CalcAv As Currency
Sub MainAvProg()
'Enter number of staff
NumStaff = InputBox("please enter num staff")
Call GetFigures
Call CalcAverage
Call TellAv
End Sub

Sub GetFigures()
'Get sales figures
Total = 0
For Count = 1 To NumStaff
Next
For Count2 = 1 To 12
Total = Total + InputBox("Please enter the sales figures")
Next
End Sub

Sub CalcAverage()
'Calculate average
CalcAv = Total / 12
End Sub

Sub TellAv()
'Tell user average
Call CalcAverage
MsgBox "The monthly sales figure average is " & Format(CalcAv, "?#,##0.00")
End Sub


thats what ive done so far i can't seem to get my head around it at all, :help prehaps i could have a few more pointers would be a great help. i do need to farm them all out to seperate sub procedures btw.

[Ken Puls]

Hi Tom,

Based on what you've posted above, you seem to want to just run the following part multiple times:



Call GetFigures
Call CalcAverage
Call TellAv

So to me, that's the part that should be looped. Looping in your subprocedure is not going to accomplish what you're after.

[stanl]

To be clear, that was not a shot at you, or your professionalism, either, so please don't take it that way.


I really apologize to you and the original poster. 'Having a bad day' is the poorest of excuses - you read in another language and end up on Jerry Springer. I meant no offense and gonna just hold off until I have something useful to contribute. Stan

[Ken Puls]

I really apologize to you and the original poster. 'Having a bad day' is the poorest of excuses - you read in another language and end up on Jerry Springer. I meant no offense and gonna just hold off until I have something useful to contribute. Stan

No worries, Stan. It's all good. :)