Sending data back to parameters

[Guest]

Hi every1!!!

Need to know if this can be done. Here is my problem.

Ive created a custum function that takes-in parameters from a few cells on
same row
its being called from.

With those parameters i do some calculations and find what needs to be found.
So far so good.

Lets say i find the following answer inside this function of mine: 45
Could i then redirect that answer to one of my parameter cell.

Senario:

Columns

L M N AF
0 0 0 (my function is here)


Lets say my funct. says that when L,M,N =0 then M should equal to : 4

Can i asign that 4 to M while still in my function.

Can this be done?

Thx for your help

PAtrick

 

[Guest]

That should be OK with just one provision. The function can not be used
directly in the cell of a spreadsheet. Declare the function private so that
it can only be accessed in code and not through the list of functions in
Excel.

HTH

 

[Trevor Shuttleworth]

Patrick

simple answer is no. A function can only return a value, not affect other
cells or formatting ... or the Excel environment in general.

Regards

Trevor

 

[Guest]

I agree with you in general. A function should not change anything within its
environment. No Side Effects. That being said, Excel does not keep you from
creating side effects. Not to mention so long as this function is private and
not accessible directly through excel, then there should be no problem. My
big question for Patrick would be what is this function supposed to return.
As a guess this is not a function at all but a sub procedure where nothing is
being returned.

I personally ensure that my functions do not create side effects, right down
to ensuring that the acitive cell is not moved... I assume you live by the
same rule and I like your coding discipline...

 

[Fredrik Wahlgren]

Hi every1!!!

Need to know if this can be done. Here is my problem.

Ive created a custum function that takes-in parameters from a few cells on
same row
its being called from.

With those parameters i do some calculations and find what needs to be found.
So far so good.

Lets say i find the following answer inside this function of mine: 45
Could i then redirect that answer to one of my parameter cell.

Senario:

Columns

L M N AF
0 0 0 (my function is here)


Lets say my funct. says that when L,M,N =0 then M should equal to : 4

Can i asign that 4 to M while still in my function.

Can this be done?

Thx for your help

PAtrick

Not from within a cell function. You should use the Worksheet_Change event
to do this.

/Fredrik

 

[Guest]

Here's a copy of my function as is.

If my 3 main columns L,M and N are empty it needs to find these values.
Now my client would like to see these values, hence the question about
sending the info back to a parameter.

Public Function Assign_L75_Value(ByVal var_air As String, _
ByVal var_L75 As String, ByVal var_style As String, _
ByVal var_tabName As String, ByVal var_ThermalS As String, _
ByVal var_FL75 As String, ByVal var_CLStr As String)

Dim CrackLTH As Double
Dim check As Boolean
Dim var_CLStrucValue As Double
Dim var_Final_L75 As String
Dim foundL75 As Double

check = checkEmptyAsignL75(var_style, var_ThermalS, var_air, var_L75)

If check Then
Assign_L75_Value = "***"
Exit Function
ElseIf var_FL75 = "-R-" Then
Assign_L75_Value = "-R-"
Exit Function
End If

'Find the value based on crack lenght and Air column value.
CrackLTH = GetCrackLenghValue(var_style, var_tabName, var_ThermalS)
If CrackLTH = 0 Then
Assign_L75_Value = "***"
Exit Function
End If

'Check if value exist in the M column, if its the case it means that
'the user actually entered a value in the L and N column.
'Here there's another function in the M column that takes values from
'The L and N columns. It returns '***' when no values are found in these 2
cols.
'So i only check for the '***'if there there it means that its empty.

If var_L75 = "***" Then 'we need to back-fill
var_CLStrucValue = GetCrackLenghStrucValue(var_style, var_tabName)

'**** Over here could i copy this value back into the appropriate column.
'**** Thats my kestion.

'Get the L75 value based on category.
Select Case var_air
Case "A2"
foundL75 = 1.65 * var_CLStrucValue
Case "A3"
foundL75 = 0.55 * var_CLStrucValue
Case "FX"
foundL75 = 0.25 * var_CLStrucValue
End Select


var_Final_L75 = Application.Round(foundL75 * (CrackLTH / var_CLStrucValue),
3)
Assign_L75_Value = var_Final_L75


Else 'we can use values entered in L,M,N columns.
var_Final_L75 = Application.Round(CDbl(var_L75) * (CrackLTH /
CDbl(var_CLStr)), 3)
Assign_L75_Value = var_Final_L75
End If


End Function

Does it make any sence with the code...
thx again

 

[Guest]

Looking at the function I would advise against it very strongly... Side
effects are a beast to deal with. It would be no fun at all if Sum changed
the source data that it was summing. The same is true here... If something
were to crash half way thoough, or there was an error in the logic... You
would be verrrryyyyyyy unhappy.

HTH

 

[Tushar Mehta]

I agree with you in general. A function should not change anything within its
environment. No Side Effects. That being said, Excel does not keep you from
creating side effects. Not to mention so long as this function is private and

Maybe, you can share an example of a user defined function that can
change which cell is the active cell and/or change contents of
arguments that are other cell references...

If you'd like a 'for instance' how about: In cell B1 I'd like to enter
=JTUDF(A1) and have function change the content of A1 and move the
active cell to J1.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions