Solved: What is Wrong with This Function?

[stanl]

Function SheetEmpty(oWS As Worksheet)
Return oWS.UsedRange.Address = "$A$1" And IsEmpty(oWS.Range("A1")) And oWS.Shapes.Count = 0
End Function


TIA

Stan

[xCav8r]

Hi, stan, :hi:

There are several things wrong with that. What do you want to accomplish?

[Bob Phillips]

Function SheetEmpty(oWS As Worksheet)
Return oWS.UsedRange.Address = "$A$1" And IsEmpty(oWS.Range("A1")) And oWS.Shapes.Count = 0
End Function


VBA Functions do not use the Return verb for its result, it sets the function name to the return vale


Function SheetEmpty(oWS As Worksheet)
SheetEmpty = oWS.UsedRange.Count = 1 And _
IsEmpty(oWS.Range("A1")) And _
oWS.Shapes.Count = 0 And _
oWS.ChartObjects.Count = 0
End Function


What about worksheet code?

What if Usedrange was bigger but was cleared?

[stanl]

Thank you. I'll mark as solved, but I don't quite understand



What if Usedrange was bigger but was cleared?


Stan

[Bob Phillips]

Thank you. I'll mark as solved, but I don't quite understand

Stan

What I mean is that is there was data on a sheet, but it was cleared off, UsedRange still points to the old data end (a 'feature'). To show this, take a balk sheet, put a value in say L10, then clear L10. If you now do a Ctrl-End, it still goes to L10!

I thought this would mean that the current function would replicate that, so oWS.UsedRange.Count would be greater than 1 even though all cells are empty, and so it would need to be tested another way. However, testing it seems to show differently. I may be messing up the testing, but it seems okay.