Need help using Excel functions in VBA sub

Question

So I'm working through this homework, and am totally stuck at putting Excel formulas in my code. This is the prompt:

3. Use Excel functions in the code (with Application.WorksheetFunction) to calculate the statistics and assign the corresponding values to variables of type double. Use the range name ScoresData when referring to the range which is provided as an argument to the functions. Remember – when using Excel functions in the code, the range specifications need to be done using VB syntax.

4. Open a single message box showing the values stored in the variables (see image on below). Tips:

a. You can use the constant vbNewLine to break a line in the prompt. For example: MsgBox “there is so much material in BIT 311” & vbNewLine & “Oich!” – will place a line break after 311.

b. You can use the constant vbTab to add a Tab space between two parts of the prompt. For example: MsgBox “Average:” & vbTab & Average

5. Finally, assign the macro to a button on the worksheet.

And my current code (not working) is:

Sub ScoresStatistics()

    Dim Average As Double
    Dim StandDev As Double
    Dim Min As Double
    Dim Max As Double
    Dim ScoresData As Range


    Range("A1").End(xlDown).Name = "ScoresData"

    Average = Application.WorksheetFunction(Average(ScoresData))
    StandDev = Application.WorksheetFunction(StDev.P(ScoresData))
    Min = Application.WorksheetFunction(Minimum(ScoresData))
    Max = Application.WorksheetFunction(Maximum(ScoresData))

    MsgBox "Here are summary measures for the scores:" & vbNewLine & "Average:" & vbTab & Average & vbNewLine & "Standard Deviation:" & vbTab & StandDev & vbNewLine & "Minimum:" & vbTab & Min & vbNewLine & "Maximum:" & vbTab & Max

End Sub

Answer 1

For starters prefix all your variable names to ensure you don't use a reserved word.

Dim dblAverage As Double
Dim dblStandDev As Double
Dim dblMin As Double
Dim dblMax As Double

End(xlDown) is a Method (i.e. action/instruction) and is typically used to locate the address, row or column number for a continuous list.

If you are trying to write the literal "Scores Data" in cell A1 the correct syntax would be Activesheet.Range("A1").value = "Scores Data"

If you are trying to write it to the next available empty row, then add a variable to find the last row first.

Dim lRow as long

'*** this is how to use End(xlup) properly
lrow = activesheet.Range("65636").End(xlup).row + 1    
Activesheet.Range("A" & lRow).value = "Scores Data"

IF you are trying to do stats on column A add: Dim ScoresData as range

'*** Intersect defines ScoresData from A1 to the last value set ScoresData = intersect(activesheet.usedrange, activesheet.range("A1").entireColumn)

The latter is what you are after. Your problem is not understanding how to properly define a range of cells for use in formulas.

Answer 2

That's a pretty reasonable first attempt but I find that you are mashing together different methods of achieving the same thing. Using half of one method and half of another rarely gets the correct result. Typically, programmers will favor one method over another. That does not mean that other methods are wrong or inefficient; just that it's easier to remember the syntax and processes from one method rather than bouncing between similar methods and possibly confusing the syntaxes as what looks like was done here.

It looks like you want to deal with a range of cells that would be selected if you were on A1 and tapped Ctrl+Shift+▼. You can a) name the range, b) assign the range to a range variable, c) deal with the coded range definition directly. Here are some examples of those three methods (there are others). The Debug.Print lines output the range's address to the VBE's Immediate Window. Use Ctrl+G or View ► Immediate Window to view the output.

    ' a) Name the range.
    '    After this you can use the range by referring to Range("ScoresData")
    Range("A1:A" & Range("A1").End(xlDown).row).Name = "ScoresData"
    Debug.Print Range("ScoresData").Address

    ' b) Assign the range to a range type variable.
    '    After this you can use the range by referring to rScoresData
    Dim rScoresData As Range
    Set rScoresData = Range(Range("A1"), Range("A1").End(xlDown))
    Debug.Print rScoresData.Address

    ' c) There are lots of ways to refer to the range directly
    '    Here are a few.
    Debug.Print Range("A1:A" & Range("A1").End(xlDown).row).Address
    Debug.Print Range(Range("A1"), Range("A1").End(xlDown)).Address
    Debug.Print Cells(1, 1).Resize(Cells(1, 1).End(xlDown).row, 1).Address
    Debug.Print Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).row, 1).Address

As you can see from the Immediate Window output all of those amount to exactly the same thing. Pick one of those and memorize how to use it. At least for now, you can forget about all of the others but you should revisit them and know how they work just in case you run into them in the future.

Your copy and paste/rename method of creating the code sample you provided was discouraging. You should have at least known the correct Excel worksheet functions and how to use them on a worksheet before attempting to write VBA code to reproduce their results. Seriously; this shows a distinct lack of effort on your part. I'll provide the correct syntax for using one of your requirements, the MIN function. You will have to research and adapt what I'm providing for all of the rest. This isn't the place to learn how to get the minimum value in a column on a spreadsheet. There are lots of other sites for that. This is a place to learn how to write code that gets the minimum from a column on a worksheet.

    ' Declare the variable. Note that I am NOT using a reserved word as the variable's name
    Dim mn As Double

    ' a) Use the Named Range.
    mn = Application.WorksheetFunction.Min(Range("ScoresData"))
    Debug.Print "Minimum value in " & Range("ScoresData").Address(0, 0, external:=True) & " is " & mn

    ' b) Use the Range type variable.
    mn = Application.WorksheetFunction.Min(rScoresData)
    Debug.Print "Minimum value in " & rScoresData.Address(0, 0, external:=True) & " is " & mn

    ' c) Use the full coded range definition
    mn = Application.WorksheetFunction.Min(Range("A1:A" & Range("A1").End(xlDown).row))
    Debug.Print "Minimum value in " & Range("A1:A" & Range("A1").End(xlDown).row).Address(0, 0, external:=True) & " is " & mn

That should be more than enough to get you started. Post back with any questions you have, noting examples, error messages, etc.