Help with VBA Coding - Creating a new function

[Bragato]

So, I made this:

Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double
SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1
<code style="font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace; font-size: 1em; overflow: auto; tab-size: 4; background: rgb(249, 249, 249); word-wrap: normal; display: block; padding: 5px 10px; max-height: 500px;">Do Until i = MenoresAssistant

SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
i = i + 1

Loop
</code>End Function


It creates a SUM of a limited smaller numbers from a range. For example =somamenores({1,2,3,4},3) results in 6
It works perfectly when I type =SOMAMENORES(...... in Excel, BUT, when I use this code:



Sub fbkestatistico()
'this sub calculates G5


LinhaMediaCria = 25
Range("G5").Select
ActiveCell.Value = "=IFERROR((2*SOMAMENORES(D13:D" & LinhaMediaCria - 1 & ",C9-1)/(C9-1))-SMALL(D13:D" & LinhaMediaCria - 1 & ",C9),"""")"

End Sub

I get the Excel error 1004: Unable to get Small property of the WorksheetFunction class


Tried using Evaluate instead of WorksheetFunction with the code below, but the function didn't work.Public Function SOMAMENORES(Intervalo As Range, Menores As Integer) As Double

SOMAMENORES = 0
Dim i As Integer
i = 1
Dim MenoresAssistant As Integer
MenoresAssistant = Menores + 1

Do Until i = MenoresAssistant

'SOMAMENORES = SOMAMENORES + Application.WorksheetFunction.Small(Intervalo, i)
SOMAMENORES = SOMAMENORES + Evaluate("SMALL(" & Intervalo & "," & i & ")")
i = i + 1

Loop

End Function


So, how can I fix this?
What am I doing wrong?
How can I make it work?

 

[JLGWhiz]

The C9 would have to equate to an integer value for the Small function to work properly. That parameter determines the ranking of the value to be returned. eg. if you want the smallest value the parameter would be 1. If the array or range being evaluated has 10 items and you want the fourth smallest, then that parameter would be 4. So if C9 is not equated to an integer value, you get an error.

 

[Bragato]

The C9 would have to equate to an integer value for the Small function to work properly. That parameter determines the ranking of the value to be returned. eg. if you want the smallest value the parameter would be 1. If the array or range being evaluated has 10 items and you want the fourth smallest, then that parameter would be 4. So if C9 is not equated to an integer value, you get an error.

Oh god yes, your answer led me to the problem. Thanks a lot!

In the begining of all the macros that are run by changing one cell, I had Application.Calculation = xlCalculationManual and by the end ...xlCalculationAutomatic, BUT, the macro fbkestatistico was trying to run with xlCalculationManual and so the C9 was a "?" on the sheet from a formula, after it all ends the "?" changes to a number. And so, with the C9 = ? the macro had that error. All I had to do was remove the Application.Calculation statement and it worked beautifuly.

 

[JLGWhiz]

Oh god yes, your answer led me to the problem. Thanks a lot!

In the begining of all the macros that are run by changing one cell, I had Application.Calculation = xlCalculationManual and by the end ...xlCalculationAutomatic, BUT, the macro fbkestatistico was trying to run with xlCalculationManual and so the C9 was a "?" on the sheet from a formula, after it all ends the "?" changes to a number. And so, with the C9 = ? the macro had that error. All I had to do was remove the Application.Calculation statement and it worked beautifuly.

You're welcome,
regards, JLG