UDF: how to get value of the argument's adjacent cell?
 

Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

how to get value of the argument's adjacent cell?
 

One way:

Function Result(vala As Range, valb As Range) As Long
Result = vala.Offset(, -1) + valb.Offset(, -1)
End Function

The problem is that you say you want to use values as arguments but you are
using the ranges that the values are in to calculate the results. The above
takes care of that disconnect.

--

Vasant



"Andrew" wrote in message
om...
Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

how to get value of the argument's adjacent cell?
 

Change your function definition to take range parameters instead of
longs

Function Result(rng1 As Range, rng2 As Range) As Long

Dim adj_vala As Long
Dim adj_valb As Long

' get value of the left adjacent cell of vala
adj_vala = rng1.offset(0,-1).value
adj_valb = rng2.offset(0,-1).value
Result = adj_vala + adj_valb

End Function

Tim.

"Andrew" wrote in message
om...
Hi
I need to write a function which will use two arguments, e.g. vala
and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know
how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic
I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

how to get value of the argument's adjacent cell?
 

You got solid advice.
Just to add, I think you will have to change your function name from
"Result" to something else.
Excel doesn't seem to like it as a UDF name.


"Andrew" wrote in message
om...
Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

how to get value of the argument's adjacent cell?
 

Hi Andrew,

Be aware that your function might not recalculate when expected.
Excel will recalc when (one of) the arguments change, not cells adjacent to
the arguments.
--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Andrew" wrote in message
om...
Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

UDF: how to get value of the argument's adjacent cell?
 

Add the function below into a module

Function Res(ValA, ValB)
Res = ValA * ValB
End Function


in cell B4 (or anywhere) type

=res(A1,A3)

DavidP

On 12 Apr 2004 21:42:12 -0700, (Andrew) wrote:

Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

how to get value of the argument's adjacent cell?
 

I may be missing something, but surely you would pass the 2 cells to be
worked upon as arguments to that function and use them.

Thus if you wanted to work on B4 and B5 you would cal.l
=myFunction(B4,B5)

To use
=myFun ction(B3,B4)
and offset them seems pervers to me.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Andrew" wrote in message
om...
Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

UDF: how to get value of the argument's adjacent cell?
 

David Prout wrote in message . ..
Add the function below into a module

Function Res(ValA, ValB)
Res = ValA * ValB
End Function


in cell B4 (or anywhere) type

=res(A1,A3)

DavidP

On 12 Apr 2004 21:42:12 -0700, (Andrew) wrote:

Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

Hi David, thanks for your reply but I need to get values of A1 and A3
inside my function, and my function shouldn't use A1 and A3 value as
arguments, arguments are B1 and B3, not A1 and A3!!! As I said this is
a simplified logic I am going to use, complete solution will scan
adjacent cell untill a condition will be met. In order to implement
this I have to somehow get A1 and A3 values in my function.

UDF: how to get value of the argument's adjacent cell?
 

And did the many other solutions offered not accomplish that?

--

Vasant

"Andrew" wrote in message
om...
David Prout wrote in message
. ..
Add the function below into a module

Function Res(ValA, ValB)
Res = ValA * ValB
End Function


in cell B4 (or anywhere) type

=res(A1,A3)

DavidP

On 12 Apr 2004 21:42:12 -0700, (Andrew) wrote:

Hi
I need to write a function which will use two arguments, e.g. vala and
valb (not adjacent) as input values. This function should refer
adjacent cells of vala and valb to calculate result for example:

A B
1 adj_vala vala

2

3 adj_valb valb

4 adj_vala + adj_valb

So UDF to calculate B4, as I see it, will lool like (I don't know how
to get adj_vala and adj_valb in the UDF below):

Function Result(vala As Long, valb As Long) As Long

Dim adj_vala As Long
Dim adj_valb As Long


' get value of the left adjacent cell of vala
adj_vala = ???

' get value of the left adjacent cell of valb
adj_valb = ???

Result = adj_vala + adj_valb

End Function


The example above describes simplifed version of more complex logic I
have to implement, but idea will be the same...

Thanks a lot,
Andrew

Hi David, thanks for your reply but I need to get values of A1 and A3
inside my function, and my function shouldn't use A1 and A3 value as
arguments, arguments are B1 and B3, not A1 and A3!!! As I said this is
a simplified logic I am going to use, complete solution will scan
adjacent cell untill a condition will be met. In order to implement
this I have to somehow get A1 and A3 values in my function.