PDA

View Full Version : Solved: VBA average and standard deviation homework help



gaross
10-23-2012, 04:58 PM
I'm having a lot of trouble at the end of our VBA section in my Software Tools for Engineers course. I'm totally lost and the instructor has been no help with any questions I've had. I did fine with Excel and the beginning of using VBA with Excel and now I've hit a wall. Any help would be greatly appreciated.

Here is my problem statement:

"Work problem 5 on page 326 (attached jpg). An array variable must be used to store the data from the worksheet. There must be instructions on the worksheet telling the user what information is required and where it goes in order to perform the calculations. The code will be executed by clicking a command button. The Exit Sub statement may be useful in this problem."

Here is what I've got:

Option Explicit
Private Sub average_and_standard_deviation_Click()
Dim x As Boolean
If Range("A4") = "" Then
MsgBox "To take the average and find the standard deviation, at least two numbers must be entered", , "Error"
ElseIf Range("A5") = "" Then
MsgBox "To take the average and find the standard deviation, at least two numbers must be entered", , "Error"
Else
x = True
End If
Call mean(x)
End Sub

Function mean(x)
Dim n As Integer, i As Integer, sum As Double, Arr() As Single, average As Double
If x Then
sum = 0
For i = 1 To n
sum = sum + Arr(i)
Next i
average = sum / n

Call StdDev(average, n, i, Arr)

End If
End Function

Public Function StdDev(average, n, i, Arr())
Dim avg As Double, SumSq As Double, StdDev As Double
avg = average(n, Arr)

For i = 1 To n
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i
StdDev = Sqr(SumSq / (n - 1))

Call compute(average, StdDev)

End Function
Public Sub compute(mean, StdDev)
Dim a As Integer
For i = 4 To 5
Arr(i) = Sheets("Average").Cells(i, 1)
Next i
average = mean(2, Arr)
Std_Dev = StdDev(2, Arr)
Sheets("Average").Cells("C4") = average
Sheets("Average").Cells("D4") = Std_Dev
End Sub


I've also got an ActiveX command button to trigger the program in Excel.

Please help if you can.:help

Bob Phillips
10-24-2012, 12:05 AM
You have stated the problem as given to you, but not what your difficulty is. I haven't looked at the code in detail, but where is it lacking re the problem statement?

gaross
10-24-2012, 02:02 PM
I have very little experience with VBA. We've discussed it for about 3 weeks and now we are moving on to Mathcad. The instructor posted this on my first attempt to submit the code I posted:

"The array variable has not been given a size. Since the code will not run, I cannot evaluate most of the program. However, it appears that one (or more) variable(s) used in the mean procedure does not have a value assigned. The code is (unnecessarily) complex due to the use of multiple procedures; I think this may cause you more problems than it is worth."

The textbook doesn't cover the material very well since the instructor had "topics left out" to save money on printing. I don't get partial credit for any assignments; its either pass or fail and its due this friday. The instructor told us at the beginning of the semester that over half the class usually drops the course or fails. I should have taken the warning.

Bob Phillips
10-24-2012, 02:16 PM
"The array variable has not been given a size ...."

That is not necessarily a pre-requisite, I can show many example where an array is not given a size when declared, indeed if you don't know how big it will be it can be better not to.


Since the code will not run, I cannot evaluate most of the program...

Surely, he should be telling you why it doesn't run, the obvoious things you need to correct. There is a duty on him to also do some work.


However, it appears that one (or more) variable(s) used in the mean procedure does not have a value assigned...

SO let him tell you which aren't that should be.


The code is (unnecessarily) complex due to the use of multiple procedures; I think this may cause you more problems than it is worth."

That is a lazy statement. Multiple procedures can reduce complexity, they rarely add complexity. Monolithic procedures are a bad practice, no question.

But you still do not say what your question for us is.

gaross
10-24-2012, 03:43 PM
I don't understand how this works. I've basically created a program based on what I thought was needed, which was obviously wrong. I pulled the code from various places on this forum and elsewhere and tried to piece it together. When asking my instructor for help, I get useless information in return. Where do I start? What am I doing wrong? Not sure if I posted the jpg from my textbook or not; its not showing up for me. I think I'm completely lost on this one. This is my last assignment in VBA and about ready to throw in the towel and take a zero. My instructor told me that not everybody gets it. When we covered Excel, I finished all of my assignments. Now with VBA, I've only finished 65% (if I miss this one). So far so good with Mathcad though.

JKwan
10-25-2012, 12:43 PM
Here is my stab at it (I fixed the calc and works now)
Create a button on your sheet and call it "butCalculate"
Private Sub butCalculate_Click()
Dim ToAverage() As Double
Dim LastRow As Long
Dim lRow As Long
Dim lAverage As Double

If (Cells(4, "A") = "") Or (Cells(5, "A") = "") Then
MsgBox "Cell A4 and A5 must not numeric, cannot be blank", vbCritical, "Missing Info"
Cells(3, "A").Select
Else
LastRow = Range("A65536").End(xlUp).Row
For lRow = 4 To LastRow
ReDim Preserve ToAverage(lRow - 4)
ToAverage(lRow - 4) = Cells(lRow, "A")
Next lRow
lAverage = CalcAverage(ToAverage)
MsgBox "Your average from cells A4:A" & LastRow & " is " & _
Format(lAverage, "##.00"), , "Average"
MsgBox "Your Standard Deviation from cells A4:A" & LastRow & " is " & _
Format(CalcStdDev(ToAverage, lAverage), "###.00"), , "Standard Deviation"
End If
End Sub
Function CalcAverage(ToAverage() As Double) As Double
Dim lRow As Long
Dim lSum As Double

lSum = 0
For lRow = 0 To UBound(ToAverage)
lSum = lSum + ToAverage(lRow)
Next lRow
CalcAverage = lSum / (UBound(ToAverage) + 1)
End Function
Function CalcStdDev(ToAverage() As Double, lAverage As Double) As Double
Dim lRow As Long
Dim lSumSq As Double

lSumSq = 0
For lRow = 0 To UBound(ToAverage)
lSumSq = lSumSq + (ToAverage(lRow) - lAverage) ^ 2
Next lRow

CalcStdDev = Sqr(lSumSq / UBound(ToAverage))
End Function

Kenneth Hobs
10-25-2012, 01:18 PM
Why would you reinvent the wheel? Excel has those sort of functions built-in. Look at Application.WorkSheetFunction. functions.

See this rule about posting homework problems.

http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item

gaross
10-25-2012, 01:29 PM
Why would you reinvent the wheel? Excel has those sort of functions built-in. Look at Application.WorkSheetFunction. functions.

I agree 100%. I did very well in the Excel portion of the semester, but I'm having a hard time grasping VBA. The instructor hasn't been much help either.


See this rule about posting homework problems.

I apologize, I did not take the time to read the FAQs. I was in a bit of a hurry with the post. I've been looking at the forum for several weeks but just recently became a member. If I had joined sooner and began asking questions then, I probably wouldn't have posted my entire assignment like that and wouldn't be frantically rushing to get my assignments done.

gaross
10-25-2012, 01:33 PM
JKwan, it works great. I was pretty far off with my first several attempts. Thanks again for all your help!!

JKwan
10-25-2012, 01:34 PM
To whatever it is worth. I think Gaross did all the programming. I just gave him a helping hand. If you look at the core of the code, it is his code. If he has nothing posted, I wouldn't even bother, because he did everything himself (mostly), I helped him (I hope). I remember when I first started, it was no fun.

gaross
10-25-2012, 01:46 PM
To whatever it is worth. I think Gaross did all the programming. I just gave him a helping hand. If you look at the core of the code, it is his code. If he has nothing posted, I wouldn't even bother, because he did everything himself (mostly), I helped him (I hope). I remember when I first started, it was no fun.

I spent a lot of time spinning my wheels over the last several days. The code I posted at the top was my 4th attempt at starting over. I asked my instructor for help or direction and got nothing.

Again, thanks for all your help!

Kenneth Hobs
10-25-2012, 02:25 PM
I took GWBasic in college and Fortran77 while pursuing a Civil Engineering degree in 1989. I tested out of Lotus123 but we did not do LotusScript macros back in those days all that much. Basically, if you know one computer language, learning most others are easier.

Where you can get in trouble applying a canned solution is in the understanding of functions in the solution used. In this case, understand that you are looking at the sample standard deviation and not the population standard deviation. Sample standard deviation is what I typically use in my projects.

While a range is a matrix and sort of an array, this example makes it literal.

I already gave you the solution method so I just detailed the other requirements. As you can see, you really only need one line of code if you ignore error checking routines and the requirements of the problem solution requirement details that are trivial.

If your button is on the Average sheet, you can skip alot of the worksheet code parts. The ActiveX Command button code in the sheet:

Private Sub CommandButton1_Click()
Homework
End Sub
In a Module:
Sub Homework()
Dim r As Range, a() As Variant, w As Worksheet

If Not WorkSheetExists("Average") Then
MsgBox "The Worksheet named Average, does not exists!", vbCritical, "Macro Ending"
Exit Sub
End If

Set w = Worksheets("Average")
With w
Set r = .Range("A4", .Range("A" & Rows.Count).End(xlUp))

If WorksheetFunction.Count(r) < 2 Then
MsgBox "A minimum of two data points are needed for the average and standard deviation funtions!", vbCritical, "Macro Ending"
.Activate
.Range("A3").Select
Exit Sub
End If

a() = r

MsgBox "Average: " & WorksheetFunction.Average(a) & vbLf & _
"Sample Standard Deviation: " & WorksheetFunction.StDev(a), vbInformation

.Activate
.Range("A3").Select
End With
End Sub

Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
Dim ws As Worksheet, wb As Workbook
On Error GoTo notExists
If sWorkbook = "" Then
Set wb = ActiveWorkbook
Else
Set wb = Workbooks(sWorkbook)
End If
Set ws = wb.Worksheets(sWorkSheet)
WorkSheetExists = True
Exit Function
notExists:
WorkSheetExists = False
End Function