Excel function "AVERAGE" won't take a variable

Question

I want to compute the average of a range of cells for several rows (in the adjacent column), and vary the first cell in the column using the variable “x”.  I put “Dim x as Integer” in the declarations.  This is the VBA code:

Cells(row, "I").FormulaR1C1 = "=AVERAGE(R[-x]C[-1]:RC[-1])"

I get an error code “Application defined or object defined error” with anything other than a number.  Is there any Dimension statement that would run?  Thanks very much

Answer 1

I have the impression that dimension “integer” might run code faster than “long”.

At the machine instruction level, it no longer makes any difference.  That used to be true of PCs before the 1990s.

I cannot speak for VBA with impunity.  Since VBA is an interpreter to some degree, it is unclear if and how much code is compiled into machine language.

Performance measurements are tricky due to idiosyncracies that I attribute to VBA as well as external factors beyond our control (interrupts, cache misses, VBA loop overhead, etc), all of which cause a great deal of variability.  And of course, it might vary from one computer to another.

Ironically, on my computer, simple type Integer arithmetic (x=x+1) seems to be about 21% slower(!) than type Long arithmetic.

(EDIT.... I'm skeptical of that conclusion.  But even if it's right, I doubt that you will see any significant performance difference if you change all type Integer to type Long.  Any integer calculation is probably a small percentage of the overall execution time.  And that's really the point.)

Of course, the bigger difference is storage space.  But that should matter only if you have humongous arrays (read: multi megabytes),

IMHO, the most important thing is reliability and flexibility of the code.  Sooner than later, "integer overflow" will come to haunt you, if you use type Integer.

---

Aside.... It is also important to use type Double instead of type Single.  Again, there is no performance difference at the machine instruction level.  But there is a precision issue that will bite you if/when you store a type Single value into an Excel cell.  For example, Cells(1,1) = CSng(1.23) results in the value 1.23000001907348.

Not sure how I can use any designation other than “row” (versus “r”)?

Are we talking about the same thing?

I was merely commenting about the choice of variable names, not the concept of a row number.

Obviously, we are free to choose (almost) any name for a variable.

I am merely suggesting that we avoid names that VBA uses for (common) properties, even if VBA itself is okay with it.

Sigh, VBA does not make that easy to do.  When I suspect that my variable name might be a VBA property name, I test it by entering a simple statement, for example x = row (lowercase).  When VBA changes "row" to "Row" automagically, I know that VBA uses that name in some context.

But I draw the line at "z".  So at the end of the day, it is just a matter of personal preference.

Answer 2

I was wondering why do you want to use R1C1 as rarely anybody uses. Good that your problem is solved.

Answer 3

Would either of the following be easier for you:

Dim r As Long, x As Long

[....]

Cells(r, "i").Formula = _

    "=average(" & Range(Cells(r - x, "h"), Cells(r, "h")).Address(False, False) & ")"

or

Dim a As Double

[....]

a = WorksheetFunction.Average(Range(Cells(r - x, "h"), Cells(r, "h")))

The first form puts an AVERAGE formula into the worksheet.  There is no need to use the R1C1 form, if you find that difficult, even if the worksheet is in R1C1 mode.

But if you simply want to "compute the average", the second form does that directly in VBA.  There is no need to create an Excel formula, unless that is truly what you want.

BTW, note the use of type Long instead of Integer.  That is always prudent to do, even if you do not expect more than 32767 cells of data.  It is especially important for "r", since that is a row number.

Also note the use of "r" instead of "row".  It is a good idea to avoid VBA keywords and property names, even if VBA can disambiguate them in some contexts.  You can always choose a more descriptive name like "myRow".

Answer 4

Thank you for the response.  The following code works:

Cells (row, "I") = WorksheetFunction.Average(Range(Cells(row - x, "H"), Cells(row, "H")))

Unfortunately, this displays my ignorance of VBA after all these years.  I am still learning.

Again, thanks to both of you for the help.  I would give credit to joeu2004 for the best answer.

Perhaps I could ask a few (relatively unimportant) questions for joeu2004.

For background, I run a macro “master” that creates a spreadsheet for a given stock market security with date (usually 300 dates) open/high/low/close/volume in 6 columns.  Then I call about 10 macros (from “master”) that perform technical analysis on the data (and chart).  I total about 1000 lines of code in all the macros, and I run about 200 securities per day, so I care how quickly the code runs.

1.       I have the impression that dimension “integer” might run code faster than “long”.  Mostly why I use it if, in fact, it is an integer.

2.       Throughout the code, I use

·         For row = 15 to numdys (number of days/rows)

·         Cells (Row, Column) = some manipulation of the data

·         Next

Not sure how I can use any designation other than “row” (versus “r”)?

3.       New word: “disambiguate” (VBG).  Saving that for my next scrabble game!!

Answer 5

Hello billrobpv

I am V. Arya, Independent Advisor, to work with you on this issue. Use below one

Cells(row, "I").FormulaR1C1 = "=AVERAGE(R[" & -x & "]C[-1]:RC[-1])"