Help with VBA Coding - Creating a new function

Bragato

New Member
Joined
Nov 26, 2017
Messages
7
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 26, 2017
Messages
7
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,812
Messages
5,638,489
Members
417,028
Latest member
JFCLUK

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
Top