Using a filename in worksheetFunction.Average(Range("range").offset(0.0)

I thought I had this working. I tested this at work and brought the workbook home, to do a little extra work. But when I ran the macro at home, I get an error when using this function:

value = WorksheetFunction.Average(Range("'LV_new.csv'!C2:C100").Offset(0, (i - 1) * 5))

The error I get is: "Unable to get the Average property of the WorksheetFunction class"

If I remove the filename and just leave the range, it works but operates on the current worksheet. I need the average function to reference data in a different workbook; not just a different worksheet. Should this work as written? Why would it work on one computer but not another?

Anyone have a suggestion or better way of using this function on a range in a different workbook?

Thanks,

If you have a variable declared called "value", I'd recommend you change that to something else. Excel already has a VBA paramater "Value" so that can be confusing and could even lead to accidental coding errors. Always use unique variable names, like "MyValue".

You don't reference sheets and cells in VBA the same way you do in formulas. THis would be the correct syntax in VBA:

MyValue = WorksheetFunction.Average(Sheets("LV_new.csv").Range("C2:C100").Offset(0, (i - 1) * 5))

No, I don't actually use "value". That was just an example. the actual variable is "voltage". I compute the average voltage of data stored in one file, and store it in the variable, that is in the file that contains the macro (my summary workbook).

I did make the change and it still doesn't work. The error is "Run-time error '9'; Subscript out of range", for that line. My sheet is in a differnet file than the one with this macro, if that makes any difference. But I changed the filename to the filename of the sheet that has this macro and get the same error, so that's probably not it. In any case, this suggestion doesn't seem to work.

This is the line I have cut from my code and the line that is highlighted with the error

voltage = WorksheetFunction.Average(Sheets("LV_new_test-0000.csv").Range("C2:C100").Offset(0, (i - 1) * 5))

Can "sheets()" reference a filename with extension? I would think logically sheets should reference the sheet name (without an extension), which is not what I'm trying to do. That's why I'm trying to reference that filename. I checked the help file on sheets and it says it's to reference a sheet on the active workbook but that's not what I'm trying to do. I have two different files. The macro is in one file and filling cells in that workbook. The range in the average function is a range of values in another workbook (file).

I also tried using:

voltage = WorksheetFunction.Average(WorkBooks("LV_new_test-0000.csv").Sheets(1).range("C2:C100").Offset(0,(i-1)*5))

and that gave me the same error as I had in my first post. I'm wondering if this might have something to do with a difference in versions of Excel, as I had this working fine on my Excel 2010 system at work. It just doesn't want to run on my Excel 2007 at home. Could this have anything to do with some setting? Macros are enabled but maybe it's something else related to a setting?

"Subscript out of range" means Excel cannot find an object you've referenced specifically by name. I'm going to guess there is not a sheet called LV_new_test-0000.csv. At the moment the macro line of code needs to work, what is the name of the SHEET? (Not the workbook, the sheet in the activeworkbook?)


It make it easier if I see the code in context with the rest of the macro.

Indeed, the new error is because a sheet with that name doesn't exist. I verified that by changing the name to one in the current workbook and I get the original error of.

But here's the thing I've said a number of times. I'm trying compute the average in ANOTHER workbook ("LV_new_test-0000.csv"); NOT a worksheet in the current workbook." AND, this worked at work on my work computer(excel 2010). I took the macro home and it won't work on my excel 2007. Very strange.

Here is the entire macro, that I tried. When I execute this, I get the error: "Run-time error '1004': "Unable to get the Average property of the WorksheetFunction class". But this worked (I'm 99% sure) on my other computer. I know it worked, I'm just not 100% sure if it was exactly this macro. I may have mistyped something, between the working and the not working.

Sub MyMain()
'
' Macro1 Macro
'
Dim voltage As Double
Dim current As Double
Dim power As Double

'Get current filename
file1 = ThisWorkbook.Name

'Get CSV file
file2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv),*.csv", Title:="Please select a file")
If file2 = False Then
    ' They pressed Cancel
    MsgBox "Stopping because you did not select a file"
Exit Sub
Else
    Workbooks.Open (file2)
End If

Workbooks(file1).Activate
'
Cells(6, 1).Value = "Voltage"
Cells(7, 1).Value = "Current"
Cells(8, 1).Value = "Power"

For i = 1 To 31
    Cells(6, i * 2).Select
    voltage = WorksheetFunction.Average(Range("'LV_new_test-0000.csv'!C2:C216415").Offset(0, (i - 1) * 5))
    ActiveCell.Value = voltage
    Cells(7, i * 2).Select
    current = WorksheetFunction.Average(Range("'LV_new_test-0000.csv'!D2:D216415").Offset(0, (i - 1) * 5))
    ActiveCell.Value = current
    Cells(8, i * 2).Select
    power = WorksheetFunction.Average(Range("'LV_new_test-0000.csv'!E2:E216415").Offset(0, (i - 1) * 5))
    ActiveCell.Value = power
Next
Cells(1, 1).Select
End Sub

I will explain what the macro is to do but want the program to behave fundamentally like I am trying to do vs. some other solution that uses forula in cells.

Here is what it is supposed to do.

1) The macro is in file1. And the summary data (voltage, current, and power) is to be added to cells in the current active worksheet.

2) The column spacing in the active worksheet for voltage, current, and power is 2 and the column spacing in the other worksheet is 5, with 2 unused columns (hence the variables in the cell and range)

3) I want to leave that file untouched and unchanged. I just want to open it and compute the average of data, to be
saved in the original file.

4) The data to be summarized is in file2 (ANOTHER file/workbook called "LV_new_test-0000.csv").

5) When MyMain is executed, a dialog is opened, I select file2 (containing the data). I then go back to the original workbook. I then "try" to compute the average in that other workbook.

6) Here is what I don't want to do. I don't want to simply add a formula like "=Average()" type formula, in each cell in my current worksheet, because it slows my system up very badly. I have 93 colums of data (31*3) that needs to be averaged and the number of records is over 200,000, and can be higher. I tried that but it would tie up all my resources. And my summary sheet (file1) doesn't need to continually update cells. So I decided to put the summarizing into a VBA file and just save the summary data, and do that operation only 1 time.

Finally, this worked on my other computer(Excel 2010). I'm just not sure why it's not working on my 2007 excel.

Here's the problem I'm trying to solve.
1) I want to know how to correctly reference a range of data, in another workbook

I'm sure there are lots of ways to do what I'm trying to do. But for this exercize, I'm trying to understand how to reference a range of data, in a different workbook.

Questions:
1) What is the meaning of the error that I receive?
2) What is the proper way to reference the range of data in the function used, IF that data is on a different worksheet in a different workbook?
3) I'll say again, I'm pretty sure that this worked on my other computer, as typed. I don't it worked but I made some changes before I took it home and may have introduced some subtle error. I think the key is to understand what this error means but I can't find anything saying what the error means.

Note: I realize there are many ways to solve any problem. I'm stuck on this one, so it now becomes an academic exercize, even if there's a better way of doing this. I don't like ignoring a problem and going to a different solution, unless I can solve the original problem too. So, I prefer to know why this doesn't work. Is referencing the range of cells, in a different workbook, in this function, impossible or possible? If it's possible, I'd like to know how. If it's impossible, I'm ready to move on. But I'd like to understand this referencing issue, before I abandon this attempt.