VBA - Worksheetfunction.small

T

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
I have this formula...how would i convert it to vba...

=SUM(SMALL(A1:A10,{1,2,3}))

i would think this would work but gives me an invalid character on the "{"...any thoughts?
trying to use this in a UDF...rng is the select range....

Code: 
worksheetfunction.sum(worksheetfunction.small(rng,{1,2,3})
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Sort by date Sort by votes
Since space is not an issue in VBA, and an array formula is the same processing time-wise as an equal number of individual instructions, I would just list them separately:
Code: 
Public Function Low3(rng As Range)
Dim Val As Double

    Val = WorksheetFunction.Sum(WorksheetFunction.Small(rng, 1)) + _
          WorksheetFunction.Sum(WorksheetFunction.Small(rng, 2)) + _
          WorksheetFunction.Sum(WorksheetFunction.Small(rng, 3))
          
    Low3 = Val
End Function

...or simply:
Code: 
Public Function Low3(rng As Range)
   Low3 = WorksheetFunction.Sum(WorksheetFunction.Small(rng, 1)) + _
          WorksheetFunction.Sum(WorksheetFunction.Small(rng, 2)) + _
          WorksheetFunction.Sum(WorksheetFunction.Small(rng, 3))
End Function
 
Upvote 0
How about:
<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> SumMin3(Rng <SPAN style="color:#00007F">As</SPAN> Range)<br>    SumMin3 = Evaluate("=SUM(SMALL(" & Rng.Address & ",{1,2,3}))")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>
 
Upvote 0
I had a similar thought after i posted....this is what i came up with for my UDF...

Code: 
Function hdcp(rng As Range) As Double
   j = 0: i = 0: mySum = 0
   Select Case WorksheetFunction.Count(rng)
   Case 3, 4, 5
       For i = 1 To 3
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 3 - 35) * 0.8
   Case 6, 7
       For i = 1 To 4
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 4 - 35) * 0.8
   Case 8
       For i = 1 To 5
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 5 - 35) * 0.8
   Case 9
       For i = 1 To 6
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 6 - 35) * 0.8
   Case 10
       For i = 1 To 7
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 7 - 35) * 0.8
   Case 11
       For i = 1 To 8
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 8 - 35) * 0.8
   Case 12
       For i = 1 To 9
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 9 - 35) * 0.8
   Case 13
       For i = 1 To 10
           j = WorksheetFunction.Small(rng, i)
           mySum = mySum + j
       Next i
       hdcp = (mySum / 10 - 35) * 0.8
   End Select
End Function
 
Upvote 0
no need to use the sum, if you're doing each one one at a time...

Try this
Code: 
Public Function Low3(rng As Range)
Dim i as Long, Val as Long
Val = 0
For i = 1 to 3
    Val = Val + Application.Small(rng,i)
Next i
Low3 = Val
End Function

Furthermore, VBA code is not even needed...A fairly simple cell formula will sufice.

=SUMPRODUCT(--(rng<=SMALL(rng,3)),rng)

Hope that helps...
 
Upvote 0
PA HS Teacher...that works great too...simplified it to

Code: 
Function hdcp2(rng As Range) As Double
   j = 0: i = 0: mySum = 0
   Select Case WorksheetFunction.Count(rng)
   Case 3, 4, 5
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3}))/3-35)*0.8")
   Case 6, 7
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4}))/4-35)*0.8")
   Case 8
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4,5}))/5-35)*0.8")
   Case 9
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4,5,6}))/6-35)*0.8")
   Case 10
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4,5,6,7}))/7-35)*0.8")
   Case 11
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4,5,6,7,8}))/8-35)*0.8")
   Case 12
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4,5,6,7,8,9}))/9-35)*0.8")
   Case 13
       hdcp2 = Evaluate("=(sum(small(" & rng.Address & ",{1,2,3,4,5,6,7,8,9,10}))/10-35)*0.8")
   End Select
End Function
 
Upvote 0
jonmo...
this is actually only a part of an overall project...at the point when i thought i might use a udf i just needed to get past the one issue....i got it working and then simplified with PA HS Teachers suggestion....I learned a couple things in this thread so that is always good...
but I most likely be using an array formula from Dominec in this thread...
http://www.mrexcel.com/forum/showthread.php?t=400179

thanks for all the suggestions...i learned a couple things...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
This VBA macro does not work - Why? - (expected to capitalize first letter of first word in sentence, following period & space)
Replies
9
Views
354
abramo
A
R
  • Solved
Use checkboxes to convert formulas to values in multiple cells
Replies
2
Views
327
Rainmanne
R
L
  • Question
User Defined Formula to Generate Polynomial Regression Equation
Replies
1
Views
714
Dave Patton
D
G
  • Solved
Plotting with VBA
Replies
3
Views
221
Gitad
G
T
  • Question
Application.WorksheetFunction.Linest - not working as expected.
Replies
2
Views
122
RoryA
RoryA

Forum statistics

Threads
1,221,469
Messages
6,160,027
Members
451,611
Latest member
PattiButche

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top