extract parameter VALUE form a user-defined function..

[perove]

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

[Eric White]

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
>