VBA: runtime error '424' Object required when searching for Shape

Question

I created a VBA script to transfer data from Excel to PowerPoint (both Version 2016) and want to check if there exists a specific Shape on Slide x and then copy it to Slide y.

The common solution which is also mentioned in (Existence of shapes in Powerpoint) does produce

"runtime error '424': Object required"

in line 3 of the function at For Each oSh in myPresentation.Slides(4).Shape

Function ShapeExists(ByVal ShapeName as String) as Boolean

Dim oSh as Shape

For Each oSh in myPresentation.Slides(4).Shapes
     If oSh.Name = ShapeName Then
        ShapeExists = True
        Exit Function
     End If
Next
End Function

The code where "ShapeExists" is called:

Set PowerPointApp = GetObject(class:="PowerPoint.Application")
Set myPresentation = PowerPointApp.Presentations.Add

 If ShapeExists("MSDreieck2") Then
    myPresentation.Slides(4).Shapes("MSDreieck2").Copy
    mySlide5.Shapes.PasteSpecial DataType:=0
 Else
    GoTo NACHZEITSTRAHLCOPY:
 End If

I already added the Object Library for Powerpoint 2016 under references and several others. When typing dim oSh as Shape it suggests two different "Shape" items in the list (one for Excel, one for PP) but it doesn't make a difference for the error which one I use.

As far as I'm concerned there is no other way to check if a specific Shape exists as the Shape index gets newly assigned at every run and as the number of Shapes on Slide x is not always the same in my case.

I would be very grateful for every suggestion. Thanks

`myPresentation = PowerPointApp.Presentations.Add` BTW if you are adding a presentation then how come you are trying to get the 4th slide? — Siddharth Rout, Mar 04 '20 at 09:09
I am working with VBA for only a week now. I got this from the internet and it has been working fine for me. It does not create a new presentation everytime I use ```myPresentation``` — Clemens Ambros, Mar 04 '20 at 09:13
What the above code does is create a powerpoint object and then adds a new presentation to it. Right after that you are checking if there is a shape in slide 4? A new presentation will not have slide 4. — Siddharth Rout, Mar 04 '20 at 09:15
ok, how should I write it then? ```PowerPointApp.Presentations(?).Slides(4).Shapes```? — Clemens Ambros, Mar 04 '20 at 09:16
You need to open the relevant powerpoint file. `Set myPresentation = PowerPointApp.Presentations.Open(FileName)` Where **FileName** is the name and path of the powerpoint file — Siddharth Rout, Mar 04 '20 at 09:19
the file has not been saved at this point in time, but it is called "Presentation1". I tried it and got the same error — Clemens Ambros, Mar 04 '20 at 09:24
If it is not saved then you need to work with the open file and not add a new one using `.Add` — Siddharth Rout, Mar 04 '20 at 09:25
I also tried it with ```ActivePresentation.Slides(4).Shapes``` and got runtime error '-2147221164 (80040154)': class not registered — Clemens Ambros, Mar 04 '20 at 09:27
Refresh the page. I have updated the answer. See if that helps? — Siddharth Rout, Mar 04 '20 at 09:29
I opened the presentation and started the code you posted. Error: ```Item Presentation1 not found in the Presentations collection``` The presentation is definitely called "Presentation1". I can see it at the top of the window — Clemens Ambros, Mar 04 '20 at 09:39
I just tested my code and it works. Are you sure there is an open presentaiton called `Presentation1` — Siddharth Rout, Mar 04 '20 at 09:40
Did you make any changes to my code. I just opened a new presentation and ran my code and it works just fine. — Siddharth Rout, Mar 04 '20 at 09:42
What happens if you type `msgbox PowerPointApp.Presentations.count` before `Set myPresentation = PowerPointApp.Presentations("Presentation1")` What do you get? — Siddharth Rout, Mar 04 '20 at 09:43
At what level of the code is `myPresentation` being `Dim`ed? Is this object Local to the calling procedure (so `ShapeExists` can't see it), or at the Module level? (Assuming that you *are* using Explicit Variable Declaration. If not, shove `Option Explicit` at the top of your module, and have a look into why that can be so important...) — Chronocidal, Mar 04 '20 at 09:44
Great. now replace `msgbox PowerPointApp.Presentations.count` with `msgbox PowerPointApp.Presentations(1).name`. What do you get? — Siddharth Rout, Mar 04 '20 at 10:03
@SiddharthRout I am getting "runtime error 91 object variable or with block variable not set" now — Clemens Ambros, Mar 04 '20 at 10:06

Siddharth Rout · Accepted Answer · 2021-11-12T11:11:34.810

0

Since it is available in both Excel and Powerpoint, avoid confusing the code by explicitly declaring it :)

Dim oSh As PowerPoint.Shape

or

Dim oSh As Object

If you do not explicitly declare it, it will refer to the Object from the Native Application which is Excel in this case. Object does late binding and leaves the Application to decide at runtime.

EDIT

A basic example on how to achieve what you want using LATE BINDING (The below code is Untested). Let me know if you get any errors.

Option Explicit

Dim PowerPointApp As Object
Dim myPresentation As Object
Dim oPPSlide As Object

Sub Sample()       
    '~~> Establish an PowerPoint application object
    On Error Resume Next
    Set PowerPointApp = GetObject(, "PowerPoint.Application")
    On Error GoTo 0
    
    If PowerPointApp Is Nothing Then
        MsgBox "No Powerpoint Instance found"
        Exit Sub
    End If
    
    PowerPointApp.Visible = True
    
    '~~> Work with open Presentation1
    Set myPresentation = PowerPointApp.Presentations("Presentation1")
    
    '~~> Change this to the relevant slide which has the shape
    Set oPPSlide = myPresentation.Slides(4)
    
    If ShapeExists("MSDreieck2") Then
        '
        '~~> Rest of your code
        '
    End If
End Sub


Function ShapeExists(ByVal ShapeName As String) As Boolean
    Dim oSh As Object
    
    For Each oSh In oPPSlide.Shapes
      If oSh.Name = ShapeName Then
         ShapeExists = True
         Exit Function
      End If
    Next
End Function

edited Nov 12 '21 at 11:11

answered Mar 04 '20 at 07:59

Siddharth Rout

147,039
17
206
250

both of these are producing the exact same error message – Clemens Ambros Mar 04 '20 at 08:06
is `myPresentation` declared and initialized? I do not see that in the function? – Siddharth Rout Mar 04 '20 at 08:18
yes, I declared ```Set myPresentation = PowerPointApp.Presentations.Add``` and ```Set PowerPointApp = GetObject(class:="PowerPoint.Application")``` – Clemens Ambros Mar 04 '20 at 08:53
Can you please update the quesiton with the exact code so that I can try it at my end? – Siddharth Rout Mar 04 '20 at 08:59
I updated the question and copied the part where the function is called. Unfortunately I cannot post the whole sub as it is 2k lines long and contains work-related data. – Clemens Ambros Mar 04 '20 at 09:06
Where have you declared `Set myPresentation = PowerPointApp.Presentations.Add`. I am more interested in this part of the code – Siddharth Rout Mar 04 '20 at 09:07
I declared it at the beginning of the sub. Later on in this sub, the ShapeExist function is called as shown above – Clemens Ambros Mar 04 '20 at 09:10
thank you very much for your help. The code you posted in your EDIT is exactly the same as mine except for the filepath as it is not saved at this point – Clemens Ambros Mar 04 '20 at 09:30
You are looking at the old code. Refresh the page :) – Siddharth Rout Mar 04 '20 at 09:31
@clemens `Set myPresentation = PowerPointApp.Presentations.Add` is not Declaring it. Declaring it is the `Dim myPresentation As Object` bit in Siddharth Rout's code. (i.e. tell VBA that `myPresentation` *exists*, before you start trying to *use* it) – Chronocidal Mar 04 '20 at 09:48
@Chronocidal I have ```Dim myPresentation As Object``` at the beginning of my sub – Clemens Ambros Mar 04 '20 at 10:00
@clemens If it is *inside* the `Sub` (i.e. after the `Sub WhateverItsCalled()` line), then the `ShapeExists` function can't see it - a bit like hanging up a poster inside your house versus outside your house, and expecting neighbours to notice. – Chronocidal Mar 04 '20 at 10:06

VBA: runtime error '424' Object required when searching for Shape

1 Answers1