extract parameter VALUE form a user-defined function..
 

Hi.
(this is a simplified version of the problem)
Im not to familiar with the xl object library , so any input is most welcome


I need to loop trough a worksheets cells, find all cells with a certian
function :MYFunction(Key1,Key2,Key3,Key4,value )
extract the VALUE (not the refernce-value) of the parameters and use this
values to do "something" in another function

So to find
all the cells I need i do a:

'***
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select 'grab all
forumula cells
For Each cell In Selection
If Left(cell.Formula, 11) = "=MYFunction" Then
'here i want to extract the values of the parameters)
End If
Next cell
'****

The value of cell.Formula is ex :"=MYFUNCTION(B1,B2,Sheet4!C4,12,3333)"
ie. sometimes it is a value and sometimes it is a referece to antother cell.
I need to always get the VALUE of each parameter (ex if B1 contains 66 i
want 66, not B1..uc?)

Right now I have plan to write a function GetItem() that takes a comma
separated list as input and give back the n'th item
So that the code will be something like:

'***
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select 'grab all
forumula cells
For Each cell In Selection
If Left(cell.Formula, 11) = "=MYFunction" Then
Par1=GetItem(cell.Formula,2)
Par2=GetItem(cell.Formula,3)
Par3=GetItem(cell.Formula,4)
Par4=GetItem(cell.Formula,5)
Par5=GetItem(cell.Formula,6)

'how do I get the VAUE of the Parx variabels..?


End If
Next cell
'****

But is there not a better way to do this?

And if not ..how do i extract the cells values from the cell referece, and
how can i separate a cell reference from a real value..
All the parameters in function MYFunction is type variant ...

anyone?
tnx
perove

extract parameter VALUE form a user-defined function..
 

After splitting out the values/references (using your GetItem function), you
would need to check each one to see if it was a value and not a cell
reference, e.g.,

On Error Resume Next
Par1 = Range(Par1).Value
Select Case Err.Number
Case 1004
'Excel cannot resolve numeric-only range addresses (e.g., Range("66"))
'or strings that < any Range name
'(e.g., Range("ThereIsNoRangeNamedThis")), so just get value
Par1 = Par1
Case 0
'Par1 is a cell reference, as Excel accepted it as an address;
'So value of reference has been assigned; do nothing
Case Else
'Unhandled error; handle separately or alert user
End Select

Just did some cursory checking on this. You would need to debug it further.


"perove" wrote:

Hi.
(this is a simplified version of the problem)
Im not to familiar with the xl object library , so any input is most welcome


I need to loop trough a worksheets cells, find all cells with a certian
function :MYFunction(Key1,Key2,Key3,Key4,value )
extract the VALUE (not the refernce-value) of the parameters and use this
values to do "something" in another function

So to find
all the cells I need i do a:

'***
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select 'grab all
forumula cells
For Each cell In Selection
If Left(cell.Formula, 11) = "=MYFunction" Then
'here i want to extract the values of the parameters)
End If
Next cell
'****

The value of cell.Formula is ex :"=MYFUNCTION(B1,B2,Sheet4!C4,12,3333)"
ie. sometimes it is a value and sometimes it is a referece to antother cell.
I need to always get the VALUE of each parameter (ex if B1 contains 66 i
want 66, not B1..uc?)

Right now I have plan to write a function GetItem() that takes a comma
separated list as input and give back the n'th item
So that the code will be something like:

'***
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select 'grab all
forumula cells
For Each cell In Selection
If Left(cell.Formula, 11) = "=MYFunction" Then
Par1=GetItem(cell.Formula,2)
Par2=GetItem(cell.Formula,3)
Par3=GetItem(cell.Formula,4)
Par4=GetItem(cell.Formula,5)
Par5=GetItem(cell.Formula,6)

'how do I get the VAUE of the Parx variabels..?


End If
Next cell
'****

But is there not a better way to do this?

And if not ..how do i extract the cells values from the cell referece, and
how can i separate a cell reference from a real value..
All the parameters in function MYFunction is type variant ...

anyone?
tnx
perove