Using vb module function in average() formula for sheet names.

[wwgmr]

Hello, I am trying to do a average formula for the month. I want to be able to add sheets to the workbook with out it giving errors or making the sheets as I go. I thought I knew the steps but I am getting error #REF! the error type is 4
When I debug the formula I find that this line is causing the stop.
Function FirstSheetName() As String
Application.Volatile True
--->With Application.Caller.Parent
FirstSheetName = _
.Parent.Worksheets((.Index Mod .Parent.Worksheet.Count) + 2).Name
End With

End Function

The With Application.Caller.Parent is where I am getting stuck. I am working in Excel 2000. I have used the +2 as I want to use second sheet from this one to start the formula.

My goal is to have cell formula that will be
=average('FirstSheetName():LastSheetName()'!B14)

I am not sure yet if I need to use Indirect. Right now the formulas aren't working. I would love any feed back from anyone on this. Thanks !


Eric

 

[hermanlaksko]

I am not quite sure what you want as you write that you want to add sheets to a workbook, but then you seem to goto a problem with a code that is concerned with a formular.

If you want to add a nre sheet then trye this:
Function AddSheet(SheetName)
Dim MyXL As Object
Set MyXL = GetObject(, "Excel.Application&quot

MyXL.ActiveWorkbook.Sheets.Add
MyXL.ActiveWorkbook.ActiveSheet.Name = SheetName
end function

 

[wwgmr]

I'm sorry, my fault what I am trying to do is a average for cell on each worksheet. b15 in this case. This will be for a month. My problem is if I have 31 worksheets say the month of July. My formula would be =average('July1:July31'!B15)

The problem I was having is If its new month I want to copy new worksheet so if today is 11 of July and so far my Work book has worksheets for July1-thru-July10, I want to add new work sheet which I can do no problem. The problem I have and is why I wanted to use a Custom formula to us in the average formula to take the Next worksheet name in the work book (July1) and average thru to the last worksheet. To add this to my average I wanted the formula to look like =average(NextWorksheetName():LastWorksheetName()!B51) The theory is that Nextworksheetname() will get the worksheet name and place it there. July1 to be used in the formula. Problem I have with the code I wrote is its not getting the name I get the error. #REF! the error type is 4
I hope this clears up what I was trying to do. I could be doing this totaly wrong way or doing more then needed. Just the number of worksheets in the workbook is verying so I need it to be flexable.

Thanks for quick response.

 

[cheerio]

You were almost there - you just needed a letter s

.Parent.Worksheets((.Index Mod .Parent.Worksheets.Count) + 2).Name

Ken

 

[wwgmr]

LOL well, wouldn't you know it be simple. That worked like charm Thanks to both of you for getting back to me.

 

[wwgmr]

Okay one last step. I hope. I did all this to use it in a average formula as I said. This is where I am at I have formula

=average('July1:July31'!B15)

This works fine. July1 is name of my first worksheet and July31 is last.

What I am trying to do is use my firstsheetname() to put in what ever is first sheet name where the July1 is. That way if I change month it will use the new first sheet name.
Also as the month will be added one day at a time I want it to use the lastsheetname() so it will always know which one to use. The trouble I am getting is a error #REF when I put this in and excel changes the formula to look like this

=AVERAGE('(firstsheetname()):[(lastsheetname())](lastsheetname())'!B15)

I proubly should post this to Excel or office board as well. I will let you know if I find answer. Thanks for help sofar.!

Eric

 

[cheerio]

How about using a named range for your formula. In the refers to box of the name definition you can put a formula.

Ken

 

[wwgmr]

I tried placing my function in cell and then pointing to it. Still getting errors. Tried indirect. I think I may have to back up and just write a more indepth function to do my average function for me. Something that will take in how many worksheets in the work book there are and when one is added work that into formula as well.

Thanks for the pointers. I know the function works at least. It does give me the sheet names. I just cannot get it to work in Average function. lol