AutoCalculate and UDF

[Ronald Dodge]

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.

 

[Rog]

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

 

[Ronald Dodge]

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

 

[Ronald Dodge]

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.

 

[Frank Kabel]

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!

 

[Ronald Dodge]

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.