Creating a UDF from a VBA routine...

[NWO]

Hello all.

(1) How can I convert the below routine to a UDF

(2) How can I run this routine using a sheet with about 30 different form
types, each with a string of monthly receipts? Guess what I'm asking is how
would I create a UDF in the form CYCYLETIME(INPUT REFERENCE RANGE, END
PENDING REFERENCE CELL) so I can repeat the use of the function across
several columns or rows nased on the UDFs input values.

Thank you.

Below posts refer.

Mark

This is just an example:

1. put monthly receipt values in column A
2. put the target (fixed) value in cell B1
3. enter and run this macro:


Sub Macro1()
Sum = 0
target = Cells(1, 2).Value
For i = 1 To 65536
Sum = Sum + Cells(i, 1).Value
If Sum >= target Then Exit For
denom = Sum
If i = 65535 Then Exit Sub
Next
j = i - 1
result = (target - denom) / Cells(i, 1).Value
Cells(1, 3).Value = j + result
End Sub


It will perform the calculation and enter the result in cell C1. If you are
not familiar with macros, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Expand AllCollapse All

 

[Jerry W. Lewis]

Function CycleTime(receipts As Range, target As Double)
n = 0
Sum = 0
For Each c In receipts
If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then ' count only
numeric cells
Sum = Sum + c.Value
If Sum > target Then Exit For ' don't count the value that
causes target to be exceeded
n = n + 1
denom = Sum
last = c.Value
End If
Next c
CycleTime = n + (target - denom) / last
End Function

Jerry

 

[NWO]

Darn Jerry, that was quick - thank you.

Please bear with my ignorance, but how can I make this UDF work in different
workbooks by just using the function only. In other words, how can I make it
so I can treat the function just like any other Excel function (i. e.
=sumif(value1, value 2...valuen)) with out worrying about the underlying
code. The documentation isn't helping me so much.

Thank you again Jerry.

Mark
-------------

 

[Jerry W. Lewis]

Save it in an Add-In.

Jerry

Darn Jerry, that was quick - thank you.

Please bear with my ignorance, but how can I make this UDF work in different
workbooks by just using the function only. In other words, how can I make it
so I can treat the function just like any other Excel function (i. e.
=sumif(value1, value 2...valuen)) with out worrying about the underlying
code. The documentation isn't helping me so much.

Thank you again Jerry.

Mark

 

[NWO]

Thank you again Jerry.

OK, I typed in the code in the Excel VB editor and then saved as an Add-In,
gave the file a name, then exited. When I opend the work file, go to Insert
function, the CycleTime function is not listed. What am I doing wrong here?

 

[Jerry W. Lewis]

Tools|Add-Ins and check the box beside the entry for your Add-In. If your
Add-In is not in that list, you can use the Browse button to find it. This
will make the function available for use in any workbook.

If you want to use the function from a VBA project, then you need to set a
reference from the project (in VBA Editor Tools|References)

Jerry