Creating a UDF from a VBA routine...

N

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 :)


Gary''s Student said:
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
 
J

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
 
N

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 :)
-------------
 
J

Jerry W. Lewis

Save it in an Add-In.

Jerry

NWO said:
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 :)
 
N

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?
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top