2

I am trying to create some VB code to create a macro in Excel. However I have only used Python and Java before so brand new to this, The problem is I have a cell which contains a list of numbers all seperated by commas and stored as a string e.g. 12,5,7,9. This has been caluclated from elsewhere. I want to be able to iterate through the list of numbers, split them up, convert them to an integer and then calculate the average of them depending on how many there are. I would like this to apply to the currently selected cell and display the result in the next cell along.

Here is the code I have done so far. It does not work and I cant test it as get the error Object required when I run it. Any suggestions would be great. Thanks.

Sub CalculateAverage()
    Dim contents As String
    Dim cell As Range
    Dim NumbersArray() As String
    Set cell = ActiveCell.Select()
    contents = Range(cell).value
    NumbersArray = Split(contents, ",")

    Dim count As Integer
    Dim lengthOfArray As Integer
    Dim first As Integer
    Dim last As Integer


    first = LBound(NumbersArray)
    last = UBound(NumbersArray)

    lengthOfArray = last - first
    Dim total As Integer
    Dim value As Integer

    count = 0
    While count <= lengthOfArray
        total = total + CInt(NumbersArray(count))
        count = count + 1
    Wend

    Dim average As Double
    average = total / count
    Range("A2").value = average
End Sub
litelite
  • 2,857
  • 4
  • 23
  • 33
Kerry
  • 21
  • 1
  • 2

4 Answers4

2

I would approach this with a user defined function. Something like:

Function CalculateAverage(s As String) As Double
    Dim v As Variant, i As Long, total As Double
    v = Split(s, ",")
    For i = 0 To UBound(v) 'LBound(v) = 0 
        total = total + v(i) 'VBA will coerce the values to double
    Next i
    CalculateAverage = total / (UBound(v) + 1)
End Function

It works like thus:

enter image description here

You can use this function directly from a spreadsheet, though it can be called from a sub just as well.

A couple of comments on your code:

1) Use Long instead of Integer. Integers can overflow too easily

2 Unless you have a strong reason to use integers, it is more idiomatic to use doubles for spreadsheet values.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • I would still be careful with `total = total + v(i)` becuase VBA will also use `+` to concatenate strings so if you define `total` as a variant by accident it might go wrong. (e.g. by using `Dim total, i As Long`). Also something like `total = v(0)+v(1)` would fail because VBA concatenates the strings and then converts to double. – arcadeprecinct May 24 '16 at 11:32
  • Good point -- although I would regard explicitly declaring `total` as double *is* being careful with it. – John Coleman May 24 '16 at 11:37
  • Indeed it should be enough. Especially since there's no harm in learning how to declare variables (apart from Variants) in one line. Although `total = v(0)+v(1)` fails even when total is declared as double so it's not completely fool proof (which I experienced myself just now) – arcadeprecinct May 24 '16 at 12:06
1

Just an example of how you can achive required result using Evaluate

Sub test()
    Debug.Print Evaluate("=Average(" & [A1].Value2 & ")")
End Sub

test

enter image description here

Vasily
  • 5,707
  • 3
  • 19
  • 34
  • Nice. For someone coming from Python (as @Kerry is) it should be pointed out that `Evaluate` is fairly idiomatic in VBA and doesn't have the same security concerns that `eval` is notorious for in Python. (Sorry for the multiple comments -- Stack Overflow gave me an error message 2 times in a row about resubmitting the request and then simultaneously posted a tripled comment) – John Coleman May 24 '16 at 11:34
  • @JohnColeman I have some expirience in VBA, but haven't familiar with Python, so for me is hard to add something to your comment). Thank you! – Vasily May 24 '16 at 11:48
  • On the Python tag, any answer that uses `eval` is likely to be downvoted almost automatically. Python's eval can run arbitrary code so is a security problem waiting to happen (http://nedbatchelder.com/blog/201206/eval_really_is_dangerous.html ) VBA's `Evaluate` is a lot closer to Python's `ast.literal_eval` -- which is often cited as a safe alternative to `eval` ( http://stackoverflow.com/q/15197673/4996248 ) – John Coleman May 24 '16 at 12:01
  • @JohnColeman if I will code on Python someday I will keep in mind that, thank you once again) – Vasily May 24 '16 at 12:14
0

Try changing the Set cell = ActiveCell.Select() to Set cell = Selection and contents = Range(cell).value to contents = cell.value Or simply remove the Set cell = ActiveCell.Select() line and change contents = Range(cell).value to contents = Selection.value
NOTE: That will work when single cell is selected

Code below will enter the average in column "B" for single or multiple cell (s) selected - single column selection only: 

Sub CalculateAverage()
Dim contents As String
Dim cell As Range
Dim NumbersArray() As String
Dim c As Range

Set cell = Selection

For Each c In cell

    contents = c.value
    NumbersArray = Split(contents, ",")

    Dim count As Integer
    Dim lengthOfArray As Integer
    Dim first As Integer
    Dim last As Integer


    first = LBound(NumbersArray)
    last = UBound(NumbersArray)

    lengthOfArray = last - first
    Dim total As Integer
    Dim value As Integer

    total = 0
    value = 0

    count = 0
    While count <= lengthOfArray
        total = total + CInt(NumbersArray(count))
        count = count + 1
    Wend

    Dim average As Double
    average = 0
    average = total / count

    'Assuming your data is in column "A" average will be entered in column "B"
    Cells(c.Row, "B").value = average

Next c

End Sub
Dawid SA Tokyo
  • 376
  • 1
  • 8
0

Function AvrgStr(Str1, Delim As String) 

With Application.WorksheetFunction
    
   AvrgStr = .Average(.Transpose(.FilterXML("<t><s>" & .Substitute(Str1, Delim, "</s><s>") & "</s></t>", "//s")))

End With
End Function
Abdul Qayyum Abdul Salam
  • 1
  • 1