public function having array passing out to a sub dropdown

[TTran]

Hi all.

I'm trying to using a dropdown form menu to do some calculations that
will access a function that has an array. Please look at my code and
please point out what i'm doing wrong.

Public Function Test(j As Integer) As Single
Dim Check(20) As String
Dim Result(20) As String
Dim Upper(20) As Single
Dim Lower(20) As Single
Dim UL(20) As Single
Dim LL(20) As Single
Dim i, k, j As Integer
k = 4
i = 0
j = 0
Do Until i = 20
Check(i) = Worksheets("Sheet1").Cells(k, 3)
Upper(i) = Worksheets("Sheet1").Cells(k, 4)
Lower(i) = Worksheets("Sheet1").Cells(k, 5)

If Check(i) <> "" Then
Result(j) = Check(i)
UL(j) = Upper(i)
LL(j) = Lower(i)
j = j + 1
End If
i = i + 1
k = k + 1

Loop
Test = UL(j)
End Function

Sub DropDown4_Change()
If Range("f13") = 1 Then
Application.ScreenUpdating = False
Range("c1") = Test(1)
Application.ScreenUpdating = True
End If
End Sub

Thanks.

 

[Guest]

It looks like you are trying to determine the value in column E for the last
row in column C (between rows 4 and 23) that is not empty.

Your main problem is you assign the test function the value UL(j), but j has
been incremented by 1 after you placed the last value in the array. So if
you want that last value, you should use

if j > 0 then
test = UL(j-1)
else
test = 0 ' or whatever you want if the range is empty
End if
end Sub


Also, Upper and Lower are VBA functions. You should use a different name
for your Upper and Lower arrays.

 

[TTran]

I want to be able to access any number that is stored in the UL(j) or
LL(j) array after the function Test remove all the empty rows in the
UpperLimit(i) or LowerLimit(i).

I will present the user with a combo box located on the excel
worksheets.

Sub DropDown4_Change()
If Range("f13") = 1 Then
Application.ScreenUpdating = False
Range("c1") = Test(1)
Application.ScreenUpdating = True
End If
End Sub

that sub check to see which choice the user selected then i proceed to
call the test function and pull out a particular # that is stored in
the UL(j) or LL(j) array and then do some calculations.

But it doesn't matter which index # (of j) i passthrough the function
Test() I always get the last # of the array.