Sort 3 textboxes to get an order for a specific formula

[stielo]

Hi,
I'm fairly new to VBA programming and I stumbled upon a problem concerning sorting my data from textboxes from small to big and use them in a formula to calculate the girth of a box. (Girth formula is [(adding 2 smallest dimension)*2+largest dimension])

The problem is, I keep having an error sorting my data before i can even use this formula. What I have now is:

 Dim W As Variant
    Dim D As Variant
    Dim H As Variant
    Dim Small1 As Variant
    Dim Small2 As Variant
    Dim Small3 As Variant

W = TextBox41.Value
D = TextBox40.Value
H = TextBox39.Value

 Small1.Value = Application.Small("W,D,H", 1)
 Small2.Value = Application.Small("W,D,H", 2)
 Small3.Value = Application.Small("W,D,H", 3)
TextBox58.Value = ((Small1.Value + Small2.Value) * 2) + Small3.Value

Thanks for helping. I've already been searching for this for about a week at the moment (and about 1 day on this forum).

[walruseggman]

Couple things:

• Small1 2 and 3 are not objects, and therefore don't have .Values
• AFAIK, .Small is a WorksheetFunction, not Application
• .Small requires that you pass in an array
• You don't want to use quotes around W, D, and H. Using quotes means they are strings (i.e. you're trying to pass in the letter W, instead of the value that W represents)
• I don't see a point in Dim'ing your variables as Variant, they are going to be that by default.

Try:

W = TextBox41.Value
D = TextBox40.Value
H = TextBox39.Value
Arr = Array(W, D, H)

 Small1 = WorksheetFunction.Small(Arr, 1)
 Small2 = WorksheetFunction.Small(Arr, 2)
 Small3 = WorksheetFunction.Small(Arr, 3)
TextBox58.Value = ((Small1 + Small2) * 2) + Small3

[stielo]

Thanks for offering your advice.
With the WorksheetFunction.Small, I get an error stating I can't use the worksheetfunction.
With application.small, this error is solved.
The code now however gives an error on the formula (error number 13, types don't match)

Anyway, thanks for giving the explanation on how you changed it. this will certainly help me improve my VBA skills

[walruseggman]

I'm guessing that text boxes 39 to 40 .Values are probably strings. Try to cast them as longs.

W = CLng(TextBox41.Value)
D = CLng(TextBox40.Value)
H = CLng(TextBox39.Value)

[stielo]

Thanks for helping out. It works like a charm like this.