Calculating averages in Excel workbook using VBA from within Acces

[Guest]

I'm trying to calculate an average using VBA from a recorded macro
in Excel, so basically I'm using the AVERAGE built-in function from excel.
First i'm counting the nr of records in my recordset and then that result
should be stored as a variable in the range for my average.

The variable row contains the recordcount and is an integer.


With xlObject
.Range("I2:I2").Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-2]:R[& row & ]C[-2])"
.Range("I3").Select
End With

When running the procedure I get an error message saying object is required
on the line that starts with ActiveCell.FormulaR1C1...

Does anyone see what I'm doing wrong here, or I'm I missing some piece
of code here? Many thanks.

 

[Douglas J Steele]

Access doesn't know anything about ActiveCell: it doesn't really know
anything about Excel.

If xlObject has been declared as Excel.Application, just put a period in
front of ActiveCell (since the ActiveCell is a property of the Excel
Application).

If xlObject is something else, what is it?

 

[Guest]

Assuming xlObject has been declared as a Worksheet, which is what it appears
from your code, you are missing a period before the ActiveCell:
ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-2]:R[& row & ]C[-2])"
Should be:
.ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-2]:R[& row & ]C[-2])"

I would suggest a more descriptive naming convention. xlObject could be any
type of object. How about
xlApp = Application object
xlBook = Workbook object
xlSheet = Worksheet object

Now, when you or anyone else reads your code, it is clear what you are
referencing.