get cell of function

[masterphilch]

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

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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

[masterphilch]

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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

[masterphilch]

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
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> >

[masterphilch]

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.
>

[masterphilch]

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.
>

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
> >

[Tushar Mehta]

In article <[email protected]>, [email protected] 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 <[email protected]>, [email protected] 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}

[masterphilch]

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 <[email protected]>, [email protected] 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.
>

[Tushar Mehta]

In article <[email protected]>, [email protected] 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 <[email protected]>, [email protected] 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 <[email protected]>, [email protected] 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.
> >
>

[masterphilch]

'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 <[email protected]>, [email protected] 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.
>