Spreadsheet produces error values

[GB]

I have a very large and complex spreadsheet - 6MB on the disk, about 20
worksheets, user-defined functions. It all calculates correctly, without any
obvious error values. However, if I copy a worksheet into another completely
new workbook (copying the values only, not the formulae), all the
user-defined values in my spreadsheet show a #VALUE! error. Those errors go
away again as soon as I press the F9 key.

One of the possible causes that occurs to me is that the user-defined
functions are not self-contained. For example, one of the functions will use
data from another part of the spreadsheet. It occurs to me that when the
focus is moved to a completely new spreadsheet VBA may be getting confused
and trying to find that data on the new spreadsheet. A quick test shows that
the problem does not occur if I add the worksheet with the data the function
needs into the new workbook. So, I think that must be it.

So, the problem comes down to this:
How do I persuade the VBA function to look for its data on its own 'parent'
spreadsheet, rather than on the spreadsheet that just happens to have the
focus at the moment? (Can't just use the parent spreadsheet name, as that
changes when I produce a new version.)

Tearing my hair out over this, so any input would be very welcome.

 

[GB]

Sorry to reply to my own post, but this is the function that I think is
causing the problem:

Public Function MCReductionFactor(Agex, Year)
ImprovementFactor = Sheets("MCImp").Cells(Agex - 19, Year - 1991).Value
MCReductionFactor = 1 - ImprovementFactor
End Function


This function is not called direct from the spreadsheet but is used by other
functions.

 

[Pete_UK]

What you could try is to copy the sheet into the same workbook (using
CTRL-drag) and then fix the values in that copied sheet. Make the file
window smaller, so that you have some grey area visible around it and
then drag (i.e. move) the sheet tab of the copied sheet into that grey
area. You can then use File | Save As to save the new file with a
different name, and this should not have changed anything in the
master workbook.

Hope this helps.

Pete

 

[GB]

Sorry to reply to my own post, but this is the function that I think is
causing the problem:

Public Function MCReductionFactor(Agex, Year)
ImprovementFactor = Sheets("MCImp").Cells(Agex - 19, Year - 1991).Value
MCReductionFactor = 1 - ImprovementFactor
End Function


This function is not called direct from the spreadsheet but is used by
other functions.

Yet another reply to myself, but it may be helpful if someone else has the
same problem and manages to find this post.

Using Thisworkbook to specify where the sheet with the info is seems to have
fixed it, thus:
ImprovementFactor = ThisWorkbook.Sheets("MCImp").Cells(Intx - 19, Year -
1991).Value


From the help file:
Sheets Property
Returns a Sheets collection that represents all the sheets in the active
workbook

Using this property without an object qualifier is equivalent to using
ActiveWorkbook.Sheets.