Range analisys

[eggpap]

Hi,

I need to analize one range and to set some cells values as result of that
analisys.
I have tried with an udf but I can't to set those values.

I know the udf prevents to format the cells but I thought I was able to set
cells values.
The only one think I can get from a udf is a value in the cell containing
it. Is it true?

If yes how can I solve my problem?

Emiliano

 

[JLGWhiz]

Hi Emiliano, You are correct that the UDF will return a value. Here is the
VBA UDF definition.

A Function procedure is a series of Visual Basic statements enclosed by the
Function and End Function statements. A Function procedure is similar to a
Sub procedure, but a function can also return a value. A Function procedure
can take arguments, such as constants, variables, or expressions that are
passed to it by a calling procedure. If a Function procedure has no
arguments, its Function statement must include an empty set of parentheses. A
function returns a value by assigning a value to its name in one or more
statements of the procedure.

Additional details can be obtained in the VBA help files under the title
"Function Statement".

 

[Alan Beban]

Hi,

I need to analize one range and to set some cells values as result of that
analisys.
I have tried with an udf but I can't to set those values.

I know the udf prevents to format the cells but I thought I was able to set
cells values.
The only one think I can get from a udf is a value in the cell containing
it. Is it true?

If yes how can I solve my problem?

Emiliano

Have you tried using a Sub procedure rather than a function? Or calling
the UDF from a Sub procedure? E.g.,

Sub foo1()
foo2
End Sub

Function foo2
'code to set cell values
End Function

Run foo1 to set the cell values.

Alan Beban

 

[ilia]

An in-cell UDF (or any worksheet function for that matter) can only
modify the value of the cell from which it is called. It cannot
modify font, number format, or values of other cells.

If your analysis needs to put results in other cells, you can either
have a UDF that returns an array, or a Sub that populates cells where
you want the analysis.

 

[Alan Beban]

An in-cell UDF (or any worksheet function for that matter) can only
modify the value of the cell from which it is called. It cannot
modify font, number format, or values of other cells.

If your analysis needs to put results in other cells, you can either
have a UDF that returns an array, or a Sub that populates cells where
you want the analysis.

Or a Sub procedure that calls the UDF that populates the cells.

Alan Beban

 

[eggpap]

Thank for your messages, in the meantime I have adopted
the change event of the worksheet. The analysis procedure
triggers when the user changes a value in one cell. If the target.value
equals a given string the procedure accomplishes the analysis.

However the procedure fails if the calculation option is set
to automatic since during the runtime the change event is triggered
by other UDF I must use.

Only if I set the calculation to manual I get the success of the
procedure. I have tried also to use the Application.Calculation statement
to set the calc. to manual at the start and to automatic at the end
of the change event, but it seems that they are ignored.

Is there a manner to accomplish the procedure successfully
without setting the calc. to manual?

I use Excel 2003.

Emiliano