On Jul 14, 12:44*pm, Jen wrote:
Hi,
I am making a UDF that is an engineering equation. *This equation
requires looking up data in a set table. *I want to be able to use
this in several of my spreadsheets, without having to copy the table
into each one. *Is there a way to incorporate it into my UDF so that
it can reference the table or do I have to add a sheet to my
personal.xlsb and then do it that way? *I want to make this UDF into
an add-in so that it is easier to share with collegues. *Thanks!
Jen

Hi Jen:

In this example the table is an array. The array (table) is first
filled by running the Starter sub. The UDF can then be used in the
worksheet:

Dim tablee(1 To 2, 1 To 7) As Integer
Sub starter()
For i = 1 To 7
tablee(1, i) = i
Next
tablee(2, 1) = 7
tablee(2, 2) = 11
tablee(2, 3) = 13
tablee(2, 4) = 17
tablee(2, 5) = 19
tablee(2, 6) = 23
tablee(2, 7) = 29
End Sub
Function engineering(inp As Integer) As Integer
engineering = 0
For i = 1 To 7
If inp = tablee(1, i) Then
engineering = tablee(2, 1)
Exit Function
End If
Next
End Function

If this does not help, try posting he
http://social.answers.microsoft.com/...0-047e4781dcf4