PDA

View Full Version : [SOLVED] Function acting funny



malik641
08-01-2005, 07:57 AM
What's going on with this?? I have a function procedure that searches the comments in the range of cells that is called for and returns the sum of the numbers in front of the "Keywords" that the macro searches for.

But if I have another workbook open at the same time and the workbook with the function procedure is not the active window (and when I open a new workbook), it will give me either a #Value! error OR just zero....??Why?

Check it out.
Thanks in advance!

Bob Phillips
08-01-2005, 08:12 AM
Because you are looking at ACtivesheet in the function, and when a new book is opened, trhe activesheet changes.

Replace this


For Each cmt In Activesheet.Comments

with


For Each cmt In Application.Caller.Parent.Comments

malik641
08-01-2005, 09:06 AM
Good stuff, thanks xld! (once again!)

Another quick question, what exactly does the Parent property exactly do. The only definition I got is:

Returns the Parent object for the comment (it is always a Range object).

Can I get a little more elaboration please???

Zack Barresse
08-01-2005, 09:46 AM
To get there, you must understand what the Application.Caller is. That is the cell in which the function is being called from. The Parent of your Application.Caller is the sheet it's located in, and the Parent of the is the Workbook it is located in. Here is an example ...


Option Explicit

Public Function WHOIAM()
Dim strBook As String, strSheet As String, strAddy As String
strBook = Application.Caller.Parent.Parent.Name
strSheet = Application.Caller.Parent.Name
strAddy = Application.Caller.Address
WHOIAM = "[" & strBook & "]'" & strSheet & "'!" & strAddy
End Function

This is because the function will fail if you do not use this method (at one time or another). If we used Activesheet instead, like this ...


Option Explicit

Public Function WHOIAM()
Dim strBook As String, strSheet As String, strAddy As String
strBook = ActiveWorkbook.Name
strSheet = ActiveSheet.Name
strAddy = ActiveCell.Address
WHOIAM = "[" & strBook & "]'" & strSheet & "'!" & strAddy
End Function


Now put the same formula in a cell, select a different cell than the one the formula is in and do a full recalc (Ctrl + Alt + Shift + F9) of all open workbooks, or just the activeworkbook (Ctrl + Alt + F9). It fails horribly.

Or for a more extensive test, in a 5 sheet workbook, put the formula in A1 of each of the first four sheets. Then in the fifth sheet in A1:A4, put =Sheet1!A1, =Sheet2!A1, etc. See the results after a full workbook recalc. (Or make the function volatile.)