I have been using the function shown below in Excel 2013 without any problems. The death and replacement of my computer however has meant that I now have to use Excel 2016. The code does not work properly in Excel 2016 and Excel 2013 is no longer available in my O365 subscription so I am unable to revert.
When the spreadsheet opens, all appears well but as soon ANY action requiring a recalculation in ANY cell occurs, every cell using the PrevSheet function displays “#N/A Error”. This cannot be put right using Ctl-z and the only way of correcting the problem seems to be double clicking each cell to invoke incell editing then pressing enter. See attachments for the various error messages displayed.
If the line “Application.Volitile is commented out then the error does not occur but values are not always updated however, they can be updated manually using the corrective procedure above. Behaviour is inconsistent though and it is not always apparent that the value has not been updated.
Double clicking the cell to show the cells involved in the code highlights the corresponding call reference in the current sheet rather than the previous sheet (see attachment Excel NA Problem Cell Highlight.PNG)
The code is as follows:
<<
Public Function PrevSheet(rCell As Range) As Variant
Dim wkbkP As Workbook
Dim whstP As Worksheet
Set whstP = rCell.Parent
Set wkbkP = whstP.Parent
Application.Volatile
PrevSheet = wkbkP.Worksheets(whstP.Index - 1).Range(rCell.Address).Value
End Function
>>
I have limited skills in VBA programming and the code was offered to me on another forum so I am not sure how it works, or how it is supposed to work. I would appreciate any help as posting the problem on the original forum has not produced any response.
Bookmarks