Selecting multiple individual cells for use in VBA

[sabinesF]

I would like to select individual, non-adjacent cells by mouse-click and use those cells as the range to be used within a macro. At the end of the calculation, I would like the result to appear in the status bar.

Currently, I can use the macro when the range consists of adjacent cells selected by either clicking and dragging the mouse or using shift+mouse-click. As one can do on the worksheet itself, I have tried to use ctrl+mouse-click to select non-adjacent cells, but receive an error message. I move around that by including "On Error Resume Next," but the cells selected using the control key are not included in the calculation.

How might I go about selecting non-adjacent cells to be used as the range?

Thank you.

For reference, the current code reads:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.StatusBar = "ESum: " & _
Application.Evaluate("=10*Log(Sum(10^(0.1*(" & Target.Address & "))))")

End Sub

 

[vaskov17]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim c As Range
    Dim sum As Double
    
    sum = 0
    For Each c In Target
        sum = sum + Application.WorksheetFunction.Power(10, Val(c.Value) / 10)
    Next
    Application.StatusBar = "ESum: " & 10 * Log(sum)
End Sub

 

[sabinesF]

Thank you Vaskov, much appreciated. I can now use cells individually selected by ctrl+mouse-click or ctrl+arrows.

The logarithmic summation is not correct, coming out to over double the expected value. I do not know why this is, as the current equation yields the correct answer when carried out logically.


The equation worked in the original code, when I was unable to use the ctrl+mouse-click selection. For example, selecting a range of 9 values all equal to 50 would yield a result of 59.54242509. With the current code, it yields a value of 137.101500423064.
Similarly, if one cells is selected, the "ESum" value should be the value of the single selected cell. If a single cell is selected with a value of 50, the ESum should be 50. However, such a selection currently yields 115.129254649702.

Perhaps this is more of an arithmetic issue, bu I am not convinced.

The equation is effectively 10 * Log(SUM(10^(xi/10))) where x ranges from xi to xn.

The code currently reads as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim c As Range
Dim sum As Double

sum = 0
For Each c In Target
sum = sum + Application.WorksheetFunction.Power(10, Val(c.Value) * 0.1)
Next
Application.StatusBar = "ESum: " & (10 * Log(sum))
End Sub

 

[vaskov17]

Apparently Log() returns the natural logarithm not the base 10 one, so try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim c As Range
    Dim sum As Double
    
    sum = 0
    For Each c In Target
        sum = sum + Application.WorksheetFunction.Power(10, Val(c.Value) / 10)
    Next
    Application.StatusBar = "ESum: " & 10 * Application.WorksheetFunction.Log10(sum)
End Sub

 

[sabinesF]

That works perfectly, good catch. Thank you very much!