Trying Small function in VBA

Question

I am trying to use worksheet function 'Small' in VBA as shown below

diff =Application.WorksheetFunction.Small((DR.Caption, HRC.Caption, HRA.Caption, PAD.Caption),2)

in this all captions contains some value and i need to get the next of minimum value as a message. diff is decalred variable.

I am getting error as compile error highlighting commas after each caption.

Am i doing something worng. What could be the correct code. please guide.

Answer 1

i tried with both the solutions but none is working . Let me refphrase the question.

i have code as follows and it is working

cost = Application.WorksheetFunction.Min(DR.Caption, HRC.Caption, HRA.Caption, PAD.Caption)

and now i want to change "min" in the formula to "small"

how to do this.

please help.

 

Hi,

What are DR.Caption etc? try this

Dim MyArr As Variant, cost As Double

MyArr = Array(CDbl(Dr.Caption), CDbl(HRC.Caption), CDbl(HRA.Caption), CDbl(PAD.Caption))

cost = Application.Small(MyArr, 2)

Answer 2

Dear Mike,

Thanks a lot. It works and I am so happy to see this functionning.

You are great,

Thanks a lot.

Shantanu

Answer 3

i tried with both the solutions but none is working . Let me refphrase the question.

i have code as follows and it is working

cost = Application.WorksheetFunction.Min(DR.Caption, HRC.Caption, HRA.Caption, PAD.Caption)

and now i want to change "min" in the formula to "small"

how to do this.

please help.

Answer 4

thanks for the reply. How do i get the value got from debug.print option in the variable "diff". I want to use this value in further formula.

Answer 5

It's unclear on how you are declaring (and assigning) the four xx.Caption variables but you might try Union().

diff =Application.WorksheetFunction.Small(Union(DR.Caption, HRC.Caption, HRA.Caption, PAD.Caption),2)

Alternately, assigning them into a variant array should work.

Dim vArray As Variant

vArray = Array(DR.Caption, HRC.Caption, HRA.Caption, PAD.Caption)

Debug.Print Application.Small(vArray, 2)    ◄ returns second lowest