Is there anyway to determine the address of a given cell in Excel
without using the ActiveCell.Address function. I need to do this
because I am trying to determine the address of a cell withing a user
defined function that is applied over a whole column. This way does
not allow me to use the activecell.Adress function. How do I determine
the cell address of the cell that contains the function?

---

Request for Question Clarification by hammer-ga on 26 Jun 2003 09:59 PDT

Hello Yollgate6,

1. Which version of Excel?

2. Could you post your function so we can see specifically what you
are trying to do?

- Hammer

---

Clarification of Question by yollgate6-ga on 26 Jun 2003 10:19 PDT

I am using Excel 2000.

Function MovAvg(myIssue, myIndex, length, myCol, DayOffset)


Row = myIssue * 10 + myIndex
    If myIndex >= length + DayOffset Then
        StartRow = Row - (length - (1 - DayOffset))
        Sum = 0
        For i = StartRow To StartRow + (length - 1)
        Summ = Summ + Cells(i, myCol)
        Next i
    MovAvg = (Summ / length)
        Else
        MovAvg = 0
End If


What i have done is created an "index" colume where i can reference
the row number based on that by using the Cells(Rowwindex, Col Index)
function. This process is slow, and when used on over 1500 records
takes about 3 minutes. There must be a faster way.

myCol refers to the colume number which i must specify in the inputs
for the function.

THis function is used in a col next to a col of data. It determines
the moving average of the data.

Hi, yollgate6-ga:

I think I've done this sort of thing with no running time problems.

Suggestions:

Change the arguments to your function so it simply takes the cell
range (over which the average is required) as a parameter.  You can
then do a FOR EACH cell in the cell range loop.  Some of the timing
problem may be due to the instantiation of a Cell object each time
through your loop, and my approach might speed things up because of
that.

Be aware of how Excel likes to calculate things.  If I were a betting
man, I'd say you've probably arranged the order of dependencies among
cells in a way that runs counter to the order in which Excel want to
evaluate them (from upper left to lower right).  Creating "circular"
references can cause Excel to slow way down as it must repeatedly
evaluate all the cells in order to obtain "convergence".

regards, mathtalk-ga

Mthtalk,

I orignially used your method, but it does not allow a dynamic range.
I have a cell above the colume that specifies the length of time I am
averaeing. As in if i want the average of 10 cells, i put a 10 in the
"length cell". This way, i can change the range for 1500 rows in one
or two key strokes.

Although I will look into your second paragraph and see if it speeds
up any.