From which cell is my VBA function called ?

[Helge Larsen]

I have a VBA function that is called from many cells in several worksheets in my workbook. The function performs some error trapping (data checking) and gives a message if an error is found.

However, I need to retrieve information on from where the erroneous call of the function is placed (sheet and cell).

Is it possible to get this information by VBA ?


________________________
Helge Larsen

 

[SkipVought]

Hi,

If you are using this as a User Defined Spreadsheet Function, then the only way to get the information you are looking for is to interrogate each sheet for the appropriate error message(s). I do not believe that you can do this from within the function.

for each ws in worksheets
  for each r in ws.usedrange
     if r.value = "YourErrorMessage" Then
       'do what you gotta do
     end if
  next
next

Skip,

_[red][/red]

 

[CautionMP]

HelgeLarsen,
Take a look at [tt]Application.Caller[/tt].

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[firefytr]

The address ...

Application.Caller.Address

The sheet ...

Application.Caller.Parent.Name

The workbook ...

Application.Caller.Parent.Parent.Name

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

"I have a VBA function that is called from many cells..."

What is Application.Caller going to do under these conditions?

Skip,

_[red][/red]

 

[firefytr]

Well if they are called from the worksheet, they shall all process the Application.Caller object..

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

Where does it answer this question?

"However, I need to retrieve information on from where the erroneous call of the function is placed"

Skip,

_[red][/red]

 

[firefytr]

Maybe I'm not following then. Helge, can you explain a little more exactly what kind of information you are wanting here and how you are looking to get it?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Helge Larsen]

I have a function HVL_Sort that sorts the values in InputRange according to the values in SortByRange. This function is used as an array worksheetfunction at several places in my workbook.

Inspired by CautionMP in an earlier post to this thread I use the following that gives the range from which my VBA function is called:
Application.Caller.Address(False, False, xlA1, True)

The function header :
Function HVL_Sort(ByVal InputRange As Range, _
ByVal SortByRange As Range, _
Optional ByVal Sort_Ascending As Boolean = True) As Variant

The statements :
Debug.Print InputRange.Address(False, False, xlA1, True)
Debug.Print SortByRange.Address(False, False, xlA1, True)
Debug.Print Application.Caller.Address(False, False, xlA1, True)
give the results :
'[My Book.xls]aSheet'!C11:Q11
'[My Book.xls]aSheet'!C12:Q12
'[My Book.xls]aSheet'!C13:Q13

That is : Sort the values in aSheet!C11:Q11 according to aSheet!C12:Q12 and place the result in aSheet!C13:Q13.

Error trapping :
1. InputRange and SortByRange must have the same dimensions.
2. These ranges should not be 2-dimensional, i.e. only (part of) a row or a column.
3. The generic routine called to do the actual sorting might return an error code.

If an error is recognized some information including the above 3 ranges are presented in a message box.



________________________
Helge Larsen

 

[firefytr]

A worksheet function cannot act upon another cell. The closest we can come to that is having a worksheet event code that fires on a cell change or calculate looking for a specific condition.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[Helge Larsen]

My VBA worksheet (array) function only changes the cells from which it is called. In my example '[My Book.xls]aSheet'!C13:Q13 is changed. The values of '[My Book.xls]aSheet'!C11:Q11 and '[My Book.xls]aSheet'!C12:Q12 are read.

Helge


________________________
Helge Larsen

 

[firefytr]

So what are you wanting to do here? You could print the errors to your Immediate window.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci