get cell of function
 

Hi

Can I somehow determine, in which column (A,B,C,...) a selfmade function
is called?
Or: Can I tell the function, placed f.e. in cell C1, to loop through the
column left (in this example B) of it?

thanks lot for help
materphilch

get cell of function
 

Application.Caller.Address

will get the calling cell address.

The second part could be done with

For Each cell In
Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do stuff on cell
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)


"masterphilch" wrote in message
...
Hi

Can I somehow determine, in which column (A,B,C,...) a selfmade function
is called?
Or: Can I tell the function, placed f.e. in cell C1, to loop through the
column left (in this example B) of it?

thanks lot for help
materphilch

get cell of function
 

thanks for help.
But it seems as if the For...Next doesn't work. The program does the
loop just once.
any idea what could be wrong?

the code:
For Each cell In Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do something
Next cell


thanks
masterphilch


Bob Phillips wrote:
Application.Caller.Address

will get the calling cell address.

The second part could be done with

For Each cell In
Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do stuff on cell
Next cell

get cell of function
 

If you are using it as a UDF, you cannot change any cell contents, just
doesn't work

--

HTH

RP
(remove nothere from the email address if mailing direct)


"masterphilch" wrote in message
...
thanks for help.
But it seems as if the For...Next doesn't work. The program does the
loop just once.
any idea what could be wrong?

the code:
For Each cell In
Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do something
Next cell


thanks
masterphilch


Bob Phillips wrote:
Application.Caller.Address

will get the calling cell address.

The second part could be done with

For Each cell In
Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do stuff on cell
Next cell

get cell of function
 

So I can't refer relatively to any cell from the position of my
self-made-function?
Wahts a UDF?

greez



Bob Phillips wrote:
If you are using it as a UDF, you cannot change any cell contents, just
doesn't work

get cell of function
 

first, application.Caller returns a rng reference, so you don't want to
convert it to an string address, the convert it back to a range.

The second is that EntireColumn is a singe entity, so there is nothing to
loop through

Just to illustrate in the immediate window:

? ActiveCell.EntireColumn.Count
1

so you have a column, not 65536 cells

Another thing is that it takes forever to loop through 65536 cells - surely
you don't want to do that. What do you want to do. Perhaps


set rng = Application.Caller
set r = rng.offset(0,-1)
set r = range(cells(1,r.column),cells(rows.count,r.column) .End(xlup))
For Each cell r

Next

If you do want the entire column then

For Each cell In Range(Application.Caller.Address) _
.Offset(0,-1).EntireColumn.Cells
'do something
Next cell



as Bob said, in a UDF used in a worksheet as a formula, you can't change
values or formatting in other cells, but you can read their values or
formating.

--
Regards,
Tom Ogilvy


"masterphilch" wrote in message
...
thanks for help.
But it seems as if the For...Next doesn't work. The program does the
loop just once.
any idea what could be wrong?

the code:
For Each cell In
Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do something
Next cell


thanks
masterphilch


Bob Phillips wrote:
Application.Caller.Address

will get the calling cell address.

The second part could be done with

For Each cell In
Range(Application.Caller.Address).Offset(0,-1).EntireColumn
'do stuff on cell
Next cell

get cell of function
 

A UDF is a user defined function, which is a VBA function that you use in a
worksheet.

You can read any cells on the worksheet, nut you can't write to them, just
return a value to the calling cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"masterphilch" wrote in message
...
So I can't refer relatively to any cell from the position of my
self-made-function?
Wahts a UDF?

greez



Bob Phillips wrote:
If you are using it as a UDF, you cannot change any cell contents, just
doesn't work

get cell of function
 

thanks Tom!

that's what I'd call a good reply!

What I actually want to do: Check the number of filled rows, left of the
cell I entered the function. And as soon as i got the number (perhaps I
can determine the number of filled cells in that row as limit [with a
loop]) of filled cells, I want to check, which one's got the biggest
value...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

greez for help or something...
masterphil

Tom Ogilvy wrote:
first, application.Caller returns a rng reference, so you don't want to
convert it to an string address, the convert it back to a range.

The second is that EntireColumn is a singe entity, so there is nothing to
loop through

Just to illustrate in the immediate window:

? ActiveCell.EntireColumn.Count
1

so you have a column, not 65536 cells

Another thing is that it takes forever to loop through 65536 cells - surely
you don't want to do that. What do you want to do. Perhaps


set rng = Application.Caller
set r = rng.offset(0,-1)
set r = range(cells(1,r.column),cells(rows.count,r.column) .End(xlup))
For Each cell r

Next

If you do want the entire column then

For Each cell In Range(Application.Caller.Address) _
.Offset(0,-1).EntireColumn.Cells
'do something
Next cell



as Bob said, in a UDF used in a worksheet as a formula, you can't change
values or formatting in other cells, but you can read their values or
formating.

get cell of function
 

What I actually want to do: Check the number of filled rows, left of the
cell I entered the function. And as soon as i got the number (perhaps I
can determine the number of filled cells in that row as limit [with a
loop]) of filled cells, I want to check, which one's got the biggest
value...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

greez for help or something...
masterphil

Tom Ogilvy wrote:
first, application.Caller returns a rng reference, so you don't want to
convert it to an string address, the convert it back to a range.

The second is that EntireColumn is a singe entity, so there is nothing to
loop through

Just to illustrate in the immediate window:

? ActiveCell.EntireColumn.Count
1

so you have a column, not 65536 cells

Another thing is that it takes forever to loop through 65536 cells - surely
you don't want to do that. What do you want to do. Perhaps


set rng = Application.Caller
set r = rng.offset(0,-1)
set r = range(cells(1,r.column),cells(rows.count,r.column) .End(xlup))
For Each cell r

Next

If you do want the entire column then

For Each cell In Range(Application.Caller.Address) _
.Offset(0,-1).EntireColumn.Cells
'do something
Next cell



as Bob said, in a UDF used in a worksheet as a formula, you can't change
values or formatting in other cells, but you can read their values or
formating.

get cell of function
 

set rng = Application.Caller
set r = rng.offset(0,-1)
set r = range(cells(1,r.column),cells(rows.count,r.column) .End(xlup))
numFilled = Application.CountA(r)
maxval = Application.Max(r)

If you just want the count of numbers in that range rather than all filled
cells, use Count rather than CountA

--
Regards,
Tom Ogilvy


"masterphilch" wrote in message
...
What I actually want to do: Check the number of filled rows, left of the
cell I entered the function. And as soon as i got the number (perhaps I
can determine the number of filled cells in that row as limit [with a
loop]) of filled cells, I want to check, which one's got the biggest
value...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

greez for help or something...
masterphil

Tom Ogilvy wrote:
first, application.Caller returns a rng reference, so you don't want to
convert it to an string address, the convert it back to a range.

The second is that EntireColumn is a singe entity, so there is nothing
to
loop through

Just to illustrate in the immediate window:

? ActiveCell.EntireColumn.Count
1

so you have a column, not 65536 cells

Another thing is that it takes forever to loop through 65536 cells -
surely
you don't want to do that. What do you want to do. Perhaps


set rng = Application.Caller
set r = rng.offset(0,-1)
set r = range(cells(1,r.column),cells(rows.count,r.column) .End(xlup))
For Each cell r

Next

If you do want the entire column then

For Each cell In Range(Application.Caller.Address) _
.Offset(0,-1).EntireColumn.Cells
'do something
Next cell



as Bob said, in a UDF used in a worksheet as a formula, you can't change
values or formatting in other cells, but you can read their values or
formating.

get cell of function
 

In article , says...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

You should. That will also help XL correctly hook your function into
its recalculation chain.

In general having a UDF access worksheet information outside of the
arguments passed to it is a bad idea. It makes it impossible for XL to
figure out when it needs to recalculate your function.

--
Regards,

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

In article , says...
thanks Tom!

that's what I'd call a good reply!

What I actually want to do: Check the number of filled rows, left of the
cell I entered the function. And as soon as i got the number (perhaps I
can determine the number of filled cells in that row as limit [with a
loop]) of filled cells, I want to check, which one's got the biggest
value...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

greez for help or something...
masterphil

{snip}

get cell of function
 

Does that mean, that if I pass a defined range, the function would only
be relaunched, when something in that range changes?

Tushar Mehta wrote:
In article , says...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

You should. That will also help XL correctly hook your function into
its recalculation chain.

In general having a UDF access worksheet information outside of the
arguments passed to it is a bad idea. It makes it impossible for XL to
figure out when it needs to recalculate your function.

get cell of function
 

In article , says...
Does that mean, that if I pass a defined range, the function would only
be relaunched, when something in that range changes?

Essentially, yes. Unless some other argument changed. Or something
else caused XL to decide to recalculate your function anyway.

--
Regards,

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

In article , says...
Does that mean, that if I pass a defined range, the function would only
be relaunched, when something in that range changes?

Tushar Mehta wrote:
In article , says...
I could add an argument to the function, force the user to enter a
range. But that wouldn't be a challenge ;)

You should. That will also help XL correctly hook your function into
its recalculation chain.

In general having a UDF access worksheet information outside of the
arguments passed to it is a bad idea. It makes it impossible for XL to
figure out when it needs to recalculate your function.

get cell of function
 

'Or something else caused XL to decide...' At that point I'd try to tell
XL in words what it has to do ;)

thanks for that hint. with the functions I wrote, the performance is
still acceptable but not as good as it could be - i think.

Tushar Mehta wrote:
In article , says...
Does that mean, that if I pass a defined range, the function would only
be relaunched, when something in that range changes?

Essentially, yes. Unless some other argument changed. Or something
else caused XL to decide to recalculate your function anyway.