Macro statement won't execute

[w_c_mead]

I have a simple statement in a function that attempts to set the value in a
particular cell. Instead, the macro appears to just stop executing when I
try to step into or over the statement with the debugger.

Here's the function in a simplified form:

Function aa_test_set(nsheet As Integer) As Integer
Dim isheet As Integer
isheet = nsheet
Worksheets(3).Cells(10, 2).Value = isheet
aa_test_set = 3
End Function

The offending statement seems to be the one where ...Value = isheet.

The function arguments are just used to allow stepping through the function,
and have no particular significance (I think). When I enter the function
into the calling cell, I provide an integer, such as 10, as the function
argument.

When I step into or over this statement, the debugger just quits doing
anything (apparently the function returned prematurely). The highlighted
statement in the debugger returns to white. The targeted cell value never
gets set. The cell containing the function call displays "#VALUE!". The
workbook has many worksheets, so 5 should not be out of range. (It doesn't
help to refer to the target sheet as a quoted name instead of an index.) The
target cell has format "General".

If I try to run the macro without a breakpoint set, the VB window stops with
the function name highlighted. I can step forward to the statement above,
but the same behavior occurs.

The problem occurs in more than one version of Excel (2002 is the latest
I've tried).

I've found an example statement that's very similar in the VBA help under
the topic "Referring to Cells by Using Index Numbers".

There is an error message in Excel 2002 next to the cell into which I
entered the function saying "Error in value." However, none of the help
suggestions helps me to recognize the problem.

I'm probably missing something obvious (?!?), but I'm stuck.

I'd greatly appreciate your help!

 

[Mike H]

Hi,

A function can't directly change a cell other than the one it is called from
so something like this works but isn't very useful perhaps you could expand
on what you expect to be returned if you call your function with (say) 10

Function aa_test_set(nsheet As Integer) As Integer
Dim isheet As Integer
isheet = nsheet
aa_test_set = isheet
End Function

Mike

 

[w_c_mead]

In the example with the statement
aa_test_set = isheet
I'd expect return of the integer isheet, which is 10.

In the function I specified, with
aa_test_set = 3
I'd expect a return value of (integer) 3, and I'd like to have
the outlying cell addressed by the Worksheets...Value statement
to be set to the integer isheet.

It's true the help pages showed this statement in a sub rather than a
function.
But, I thought a function was the same as a sub, except that the function is
called from within a spreadsheet instead of from within a function? I didn't
see anything pithy on this subject in Help, but there are so many usages of
the term function that I could be missing it.

If the distinction between sub and function is important, can I change a
"third party" cell by calling the sub within a function? That seems odd.
But, there must be some way to do this kind of thing?

Thanks, - Bill



- Bill

 

[w_c_mead]

It might be useful to back up and pose the problem I'm trying to solve. I'd
like to be able to use the sheet number (index) of a sheet as a function
argument so that the function can refer to cells using a
Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify
nsheet explicitly. But that is not general enough to survive if a new sheet
is added to the workbook.

I can't find a property or method that allows me to set nsheet to the
current sheet number so that I could offset it to refer to the previous or
next sheet.

I've got a kluge that works if I store the sheet number in a cell on each
sheet, but this value has to be reset by a "renumber" operation if a sheet is
inserted. Yuk!

How would you go about this?

 

[Jim Thomlinson]

Take a look at this function. It returns the value from cell A1 of the next
sheet.

Public Function Test() As Variant
Application.Volatile

Test = Application.Caller.Parent.Next.Cells(1, 1)

End Function

If there is no next sheet then it returns #Value

 

[w_c_mead]

Wow! Thanks for the info. That'll take me awhile to chew on...

- Bill

 

[Air_Cooled_Nut]

What Mike said is true, you can only return a value and NOT affect the
contents of another cell :-( I wish this were possible as I'm sure many have
had this need. Anyway, here is a way to get the sheet info:
--- Code Start ---
Option Explicit

Public Function aatestset(Optional DisplayIndex As Boolean) As Variant
Application.Volatile
If DisplayIndex Then
aatestset = ActiveSheet.Index
Else
aatestset = ActiveSheet.CodeName
End If
End Function
--- Code End ---

Not sure if you want the Application.Volatile but if so, the function will
be executed whenever there's a change event on the sheet (basically, though
probably not the correct verbage) like changing a cell's contents or hitting
the F9 function key (calculate sheet).

Though you may think your method is kludgy, it's probably the only way you
can accomplish it. Have you thought about having a VeryHidden sheet to use
as a reference sheet? That way you can use it for storing information that
the user doesn't neet to see nor allow them the ability to alter it ;-)
Simply create a sheet and name it, then in the VBA Editor select the sheet
and change its Visible property to xlSheetVeryHidden (use the Properties
Window).

 

[Jim Thomlinson]

Application.caller is the cell that the formula is in.
Parent is the worksheet that holds that cell.
Next is the next sheet (that one is kinda obvious)
Now that you have the next sheet you can grab a cell value from it.

Application.volatile forces the formula to re-calc each time a calcualtion
runs (this is necessary as there are no precedents to the formula).

I personally would not use a formula like this as it changes as sheets are
moved, added or deleted which could make the results seem unpredicatable
especially since you can not readliy audit the formula to see what it is up
to... But to each his own.

 

[w_c_mead]

Thanks to both Jim and Toby... This works. The volatile might not be needed,
but it does no harm except perhaps to over-calculate the function. The
VeryHidden sheet is an interesting thought, too, and one I'd never run across.

- Bill

 

[Jim Thomlinson]

This code is tied to the active sheet. What happens when you put this
function into sheet 1 but run a calculation from Sheet 2. It will look at the
wrong sheet I suspect.

The answer given will depend on what sheet is active the last time a calc
was run. That could result in some very unpredictable behavior.

 

[Air_Cooled_Nut]

You are correct. Just throwing out ideas.