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})
 
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
COUNT multiple SUM COUNTIFS
Replies
6
Views
502
Peter_SSs
Peter_SSs
J
  • Solved
RTE 381 Could not set the List property. Invalid property array index.
Replies
2
Views
54
jasonb75
J
D
  • Question Question
Table to Text Format & Send To Outlook
Replies
3
Views
61
Peter_SSs
Peter_SSs
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
457
abramo
A
B
  • Solved
Outlook 365 VBA permissions
Replies
2
Views
47
bclever07
B

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