Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the workbook as a
separate module for the file that I plan on using the function. As data is
modified in the workbook, all calculations are taken place EXCEPT for those
cells that involves the UDF, which throws the results off throughout the
workbook.

Is there a way to force these UDFs to run at the required times?

The only thing I been able to do to get around the issue is to have the
cells editted in some form or manner, which since the UDF that I created is
for a calculated column of a table, I have to select the range below the
header row, then do a Fill down (Ctrl-D) to force them to calculate.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Ronald, you could force the recalculation of this function
each time there is change somewhere else :

add the following line after the function header :

Application.volatile=true


Rgds

Rog

-----Original Message-----
Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the
workbook as a
separate module for the file that I plan on using the
function. As data is
modified in the workbook, all calculations are taken
place EXCEPT for those
cells that involves the UDF, which throws the results off
throughout the
workbook.

Is there a way to force these UDFs to run at the required
times?

The only thing I been able to do to get around the issue
is to have the
cells editted in some form or manner, which since the UDF
that I created is
for a calculated column of a table, I have to select the
range below the
header row, then do a Fill down (Ctrl-D) to force them to
calculate.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000


.

Hi
in addition to use application.volatile you may consider also using the
cell references as parameter for your UDF

--
Regards
Frank Kabel
Frankfurt, Germany


Ronald Dodge wrote:
Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the workbook as a
separate module for the file that I plan on using the function. As
data is modified in the workbook, all calculations are taken place
EXCEPT for those cells that involves the UDF, which throws the
results off throughout the workbook.

Is there a way to force these UDFs to run at the required times?

The only thing I been able to do to get around the issue is to have
the cells editted in some form or manner, which since the UDF that I
created is for a calculated column of a table, I have to select the
range below the header row, then do a Fill down (Ctrl-D) to force
them to calculate.

Thank you for your help. I should have mentioned it long ago, but it did
resolve the issue.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Rog" wrote in message
...
Ronald, you could force the recalculation of this function
each time there is change somewhere else :

add the following line after the function header :

Application.volatile=true


Rgds

Rog

-----Original Message-----
Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the
workbook as a
separate module for the file that I plan on using the
function. As data is
modified in the workbook, all calculations are taken
place EXCEPT for those
cells that involves the UDF, which throws the results off
throughout the
workbook.

Is there a way to force these UDFs to run at the required
times?

The only thing I been able to do to get around the issue
is to have the
cells editted in some form or manner, which since the UDF
that I created is
for a calculated column of a table, I have to select the
range below the
header row, then do a Fill down (Ctrl-D) to force them to
calculate.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000


.

For the UDF that I was asking about before, it doesn't have such
requirements. However, I do have a UDF that I am working on now, which does
require the Parameter optional declaration statement within the function arg
list. Now that I am up to this point, I like to know, is there already
something out there that determines what type of values/objects each
argument within the param array they are, and how to go about it.

I know VarType deals with Variable types, but what about object types like
is it just a straight forward variable or is it a reference to an actual
object such as a Range Object? I would tend to think there's already stuff
like this out there, so as I don't have to reinvent the wheel.

In the UDF that I'm working on now, it should mainly allow for string type
variables, which means, if it's a group of cells (Rather it be a single
range, or a set of ranges separated by commas), I would like it to work
similar to how the "SUM" function works with ranges, but only in this case,
it's string based rather than numeric based.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Frank Kabel" wrote in message
...
Hi
in addition to use application.volatile you may consider also using the
cell references as parameter for your UDF

--
Regards
Frank Kabel
Frankfurt, Germany


Ronald Dodge wrote:
Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the workbook as a
separate module for the file that I plan on using the function. As
data is modified in the workbook, all calculations are taken place
EXCEPT for those cells that involves the UDF, which throws the
results off throughout the workbook.

Is there a way to force these UDFs to run at the required times?

The only thing I been able to do to get around the issue is to have
the cells editted in some form or manner, which since the UDF that I
created is for a calculated column of a table, I have to select the
range below the header row, then do a Fill down (Ctrl-D) to force
them to calculate.

Hi
so you mean something like

public function mcat(rng as range,optional delimiter as string = "")
dim cell as range
Dim sResult as string
for each cell in rng
if cell.value<"" then
if sResult="" then
sResult=cell.value
else
sResult=sResult & delimiter & cell.value
end if
end if
next
mcat=sResult
end function

Call it or example like
=MCAT(A1:A100,"-")

Note: no error checking included, just more an example!

--
Regards
Frank Kabel
Frankfurt, Germany

"Ronald Dodge" schrieb im Newsbeitrag
...
For the UDF that I was asking about before, it doesn't have such
requirements. However, I do have a UDF that I am working on now,
which does
require the Parameter optional declaration statement within the
function arg
list. Now that I am up to this point, I like to know, is there
already
something out there that determines what type of values/objects each
argument within the param array they are, and how to go about it.

I know VarType deals with Variable types, but what about object types
like
is it just a straight forward variable or is it a reference to an
actual
object such as a Range Object? I would tend to think there's already
stuff
like this out there, so as I don't have to reinvent the wheel.

In the UDF that I'm working on now, it should mainly allow for string
type
variables, which means, if it's a group of cells (Rather it be a
single
range, or a set of ranges separated by commas), I would like it to
work
similar to how the "SUM" function works with ranges, but only in this
case,
it's string based rather than numeric based.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Frank Kabel" wrote in message
...
Hi
in addition to use application.volatile you may consider also using
the
cell references as parameter for your UDF

--
Regards
Frank Kabel
Frankfurt, Germany


Ronald Dodge wrote:
Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the workbook
as a
separate module for the file that I plan on using the function.
As
data is modified in the workbook, all calculations are taken
place
EXCEPT for those cells that involves the UDF, which throws the
results off throughout the workbook.

Is there a way to force these UDFs to run at the required times?

The only thing I been able to do to get around the issue is to
have
the cells editted in some form or manner, which since the UDF
that I
created is for a calculated column of a table, I have to select
the
range below the header row, then do a Fill down (Ctrl-D) to force
them to calculate.

For starters, I mean something like:

Public Function BusinessDay(ByVal StartDate, ByVal NDays, ByVal
HowDetermine, ParamArray Holidays()) as Date
.......
End Function

StartDate could be a date or a range containing a date

NDays could be a long integer value or a range containing a long integer for
how many official business days to go back excluding official holidays.

HowDetermine could be a long integer value or a range containing a long
integer value, but would only contain one of the following values, "0, 1, or
2" (this argument would go into effect for those holidays that falls on a
weekend, and need to determine which day that's normally considered to be a
business day to be instead, an official holiday.

Holidays would be the variant array that could contain a list of ranges
(even if just 1 range), a list of holidays in string format, or some
combination of both.

One might say to use WorkDays function as it does the same thing, but the
difference is one needs to know the dates already, and I have created a
class module that does it via VBA rather than using formulas. The purpose
of this function is simply to list the holidays in string format along with
the other 2 arguments, which then the function would determine the rest.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Frank Kabel" wrote in message
...
Hi
so you mean something like

public function mcat(rng as range,optional delimiter as string = "")
dim cell as range
Dim sResult as string
for each cell in rng
if cell.value<"" then
if sResult="" then
sResult=cell.value
else
sResult=sResult & delimiter & cell.value
end if
end if
next
mcat=sResult
end function

Call it or example like
=MCAT(A1:A100,"-")

Note: no error checking included, just more an example!

--
Regards
Frank Kabel
Frankfurt, Germany

"Ronald Dodge" schrieb im Newsbeitrag
...
For the UDF that I was asking about before, it doesn't have such
requirements. However, I do have a UDF that I am working on now,
which does
require the Parameter optional declaration statement within the
function arg
list. Now that I am up to this point, I like to know, is there
already
something out there that determines what type of values/objects each
argument within the param array they are, and how to go about it.

I know VarType deals with Variable types, but what about object types
like
is it just a straight forward variable or is it a reference to an
actual
object such as a Range Object? I would tend to think there's already
stuff
like this out there, so as I don't have to reinvent the wheel.

In the UDF that I'm working on now, it should mainly allow for string
type
variables, which means, if it's a group of cells (Rather it be a
single
range, or a set of ranges separated by commas), I would like it to
work
similar to how the "SUM" function works with ranges, but only in this
case,
it's string based rather than numeric based.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Frank Kabel" wrote in message
...
Hi
in addition to use application.volatile you may consider also using
the
cell references as parameter for your UDF

--
Regards
Frank Kabel
Frankfurt, Germany


Ronald Dodge wrote:
Office 2002, SP2
AutoCalculate Mode

I have created a UDF (User Defined Function) within the workbook
as a
separate module for the file that I plan on using the function.
As
data is modified in the workbook, all calculations are taken
place
EXCEPT for those cells that involves the UDF, which throws the
results off throughout the workbook.

Is there a way to force these UDFs to run at the required times?

The only thing I been able to do to get around the issue is to
have
the cells editted in some form or manner, which since the UDF
that I
created is for a calculated column of a table, I have to select
the
range below the header row, then do a Fill down (Ctrl-D) to force
them to calculate.