0

Following the code I found here, I'm trying to count the number of shapes in a worksheet, but I'm getting an 'overflow' error. The code I'm using is as follows:

Sub tst()
    Dim i As Integer
    i = Sheets("DB_AL").Shapes.count
    MsgBox i
End Sub

The problem, I think is the number of shapes that there are in the worksheet.

I'm not sure how I can delete them either. I tried also deleting the shapes with the code I found here:

Sub delShapes()
    Dim shape As Excel.shape
    For Each shape In Sheets("DB_AL").Shapes
        shape.Delete
    Next
End Sub

But I'm getting also an 'out of range' error.

At this stage, I'm more interested on deleting the shapes as it is making the worksheet un-usable.

Any ideas?

Selrac
  • 2,203
  • 9
  • 41
  • 84
  • 2
    Did you try using a Long type instead of an Integer for your variable i ? Shapes.count return a Long. – Teasel Apr 24 '18 at 09:13
  • Nice one. It worked with long: 82555 shapes. Any idea on how to iterate through them? – Selrac Apr 24 '18 at 09:20

4 Answers4

2

Overflow error

In your code you're trying to get the result of Shapes.Count into an Integer, the problem you're facing is because Shapes.Count return a Long.

The following code should work as you expect:

Sub tst()
    Dim i As Long
    i = Sheets("DB_AL").Shapes.count
    MsgBox i
End Sub

Deleting all shapes in your sheet

When you're using a For Each loop you do not need to declare the object, you can simply go through all shapes with the following code:

Sub delShapes()
    For Each shapeToDelete In ThisWorkbook.Sheets("DB_AL").Shapes
        shapeToDelete.Delete
    Next shapeToDelete
End Sub
Teasel
  • 1,330
  • 4
  • 18
  • 25
  • I still get an 'out of range' error with your delShapes code :-( – Selrac Apr 24 '18 at 09:28
  • @Selrac What shapes are you trying to delete? And how much? – Teasel Apr 24 '18 at 09:32
  • at this stage, I would like to delete them all really – Selrac Apr 24 '18 at 09:36
  • @Selrac Did you try copy-pasting my code? I ran it and everything works. Make sure the name of the sheet is written correctly as specified by arunv. – Teasel Apr 24 '18 at 09:42
  • "When you're using a For Each loop you do not need to declare the object" - in VBA should always be using "Option Explicit" and the loop variables must also be declared (and is good practice to do so anyway). VB.Net allows a little more leniency, but not VBA. – AJD Apr 24 '18 at 23:30
1

Over flow issue is explained by Teasel,

to delete the shapes you can use this code

Sub delShapes()
Dim shape As shape, ws As Worksheet
Set ws = ThisWorkbook.Sheets("DB_AL")
For Each shape In ws.Shapes
    shape.Delete
Next
End Sub
arun v
  • 852
  • 7
  • 19
  • Please verify your sheet name is that "DB_AL" ? or change it – arun v Apr 24 '18 at 09:33
  • Sheets("DB_AL").Shapes.count definitely works. I don't think there is a problem with the name "DB_AL" – Selrac Apr 24 '18 at 09:40
  • Please verify the sheet name in workbook and in code.. both should be same also when you are getting error which line is highlighted? – arun v Apr 24 '18 at 09:44
  • when I get the error is on the 'shape.Delete' line. The code runs for a while and then I get the error. The exact error is 'Run-time error '-2147024809 (80070057)': The specified error is out of range. – Selrac Apr 24 '18 at 09:48
1

Try The code below :

Sub delShapes()
    Dim i As Long
    Dim x As Long
    i = Sheets("DB_AL").Shapes.Count
    For x = i To 1 Step -1
         Sheets("DB_AL").Shapes(x).Delete
    Next x
End Sub
LatifaShi
  • 440
  • 1
  • 3
  • 12
0

The Overflow error is because the data you are woerking with is too big for your variable. You have defined i As Integer, but Sheets("DB_AL").Shapes.count returns a Long type, and this raises an error. Just define i As Long and it will work.

More info about Data Types

To delete all the shapes, try:

ActiveSheet.DrawingObjects.Delete
Foxfire And Burns And Burns
  • 10,665
  • 2
  • 17
  • 38