none
Why am I getting an "Object required" Here? RRS feed

  • Question

  • Sub Greenstar()
    Dim myobj As New PowerPoint.Application
    Dim temp As Variant
    DoCmd.OutputTo acOutputReport, "Greenstar", acFormatXLS, CurrentProject.Path & "\Greenstar.xls"
    Excel.Application.Visible = True
    Workbooks.Open (CurrentProject.Path & "\Greenstar.xls")
    myobj.Visible = msoCTrue
    myobj.Presentations.Open (CurrentProject.Path & "\star.pptx")
    myobj.ActivePresentation.Slides(1).Shapes(1).Copy
    ReDim temp(5)
    For Each cell In Range(Rows(2), Rows(Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells.SpecialCells(xlCellTypeConstants).Cells
       Select Case cell.Column
            Case 1
                'ReDim Preserve temp(0)
                Set temp(0) = myobj.Presentations.Add
            Case 2
                'ReDim Preserve temp(1)
                Set temp(1) = temp(0).Slides.Add(temp(0).Slides.Count + 1, ppLayoutBlank)
            Case 3
                'ReDim Preserve temp(2) 'Country_Code
                If temp(1).Shapes.Count = 0 Then Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 15, 100, 10) Else Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height + 15, 100, 10)
                temp(2).TextFrame.TextRange.Text = cell.value
            Case 4
                'ReDim Preserve temp(3) 'Process_Name
                Set temp(4) = temp(1).Shapes.Paste
                If temp(1).Shapes.Count = 1 Or cell.Offset(-1, -1).value <> "" Then
                    Set temp(3) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(2).Top + 15, 200, 10)
                     With temp(4)
                        .Left = 10
                        .Top = temp(3).Top + 15
                    End With
                Else
                    Set temp(3) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, temp(3).Left + temp(3).Width, temp(2).Top + 15, 200, 10)
                    With temp(4)
                       .Left = temp(3).Left
                       .Top = temp(3).Top + 15
                    End With
                End If
                temp(3).TextFrame.TextRange.Text = cell.value
       End Select
    Next cell
    End Sub

    why am I getting a Run-time error '424'

    Object required

    in the else section of the code below:

    If temp(1).Shapes.Count = 0 Then Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 15, 100, 10) Else Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height + 15, 100, 10)


    Thursday, September 11, 2014 10:59 AM

All replies

  • Hello,

    Temp is defined as a variant. In this case I would define it as a collection.

    On the other hand, try to find out on which part you get the error: the line you quote could give an error for mulitiple reasons.

    Best regards

    Thursday, September 11, 2014 11:15 AM
  • Hi Wouter,

                   Thanks to your reply. My question is to why temp(4) expires when it comes to Case 3?

    On the Case 4. I am able to set the following properties of that shapes (picture)

    With temp(4)

    .Left = temp(3).Left

    .Top = temp(3).Top + 15

    End With

    but when it comes to Case 3 again, I am not able to read the same properties set to it.

    Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height + 15, 100, 10)


    Thursday, September 11, 2014 11:28 AM
  • Don't know why it is working now?

     

    [CODE]

    Sub Greenstar()

    Dim myobj As New PowerPoint.Application

    Dim temp As Variant

    DoCmd.OutputTo acOutputReport, "Greenstar", acFormatXLS, CurrentProject.Path
    & "\Greenstar.xls"

    Excel.Application.Visible = True

    Workbooks.Open (CurrentProject.Path & "\Greenstar.xls")

    myobj.Visible = msoCTrue

    myobj.Presentations.Open (CurrentProject.Path & "\star.pptx")

    myobj.ActivePresentation.Slides(1).Shapes(1).Copy

    ReDim temp(5)

    For Each cell In Range(Rows(2),
    Rows(Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells.SpecialCells(xlCellTypeConstants).Cells

    Select Case cell.Column

    Case 1

    'ReDim Preserve temp(0)

    Set temp(0) = myobj.Presentations.Add

    Case 2

    'ReDim Preserve temp(1)

    Set temp(1) = temp(0).Slides.Add(temp(0).Slides.Count + 1, ppLayoutBlank)

    Case 3

    'ReDim Preserve temp(2) 'Country_Code

    If temp(1).Shapes.Count = 0 Then Set temp(2) =
    temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 15, 100, 10) Else
    Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10,
    temp(4).Top + temp(4).Height + 15, 100, 10)

    temp(2).TextFrame.TextRange.Text = cell.value

    Case 4

    'ReDim Preserve temp(3) 'Process_Name

    Set temp(4) = temp(1).Shapes.Paste

    If temp(1).Shapes.Count = 1 Or cell.Offset(-1, -1).value <> "" Then

    Set temp(3) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10,
    temp(2).Top + 15, 200, 10)

    temp(4).Left = 10

    Else

    Set temp(3) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal,
    temp(3).Left + temp(3).Width, temp(2).Top + 15, 200, 10)

    temp(4).Left = temp(3).Left

    End If

    temp(3).TextFrame.TextRange.Text = cell.value

    temp(4).Top = temp(3).Top + 15

    End Select

    Next cell

    End Sub

    [/CODE]

     

    Just removed the with block and entered like this:

     

    temp(4).Left = temp(3).Left

    temp(4).Top = temp(3).Top + 15

     

    It Does make a Difference? I want to the rootcause of it. Can anyone help me
    understand. Are these two different?

     

    [CODE]

    With temp(4)

    .Left = temp(3).Left

    .Top = temp(3).Top + 15

    End With

    [/CODE]

     

    [CODE]

    temp(4).Left = temp(3).Left

    temp(4).Top = temp(3).Top + 15

    [/CODE]

    Thursday, September 11, 2014 12:19 PM
  • Hi Prabhakaran,

    >>It Does make a Difference? I want to the rootcause of it. Can anyone help me
    understand. Are these two different?

    [CODE]

    With temp(4)

    .Left = temp(3).Left

    .Top = temp(3).Top + 15

    End With

    [/CODE]

    [CODE]

    temp(4).Left = temp(3).Left

    temp(4).Top = temp(3).Top + 15

    [/CODE]<<

    The With statement lets you specify an object or user-defined type once for an entire series of statements. With statements make your procedures run faster and help you avoid repetitive typing.

    So I think there are nothing different. You can get more detail about using with statements from link below:
    Using With Statements

    Based on the description and the error message, in my option, this error more relative to the specific environment.

    >>

    Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height + 15, 100, 10)

    <<

    I suggest that you break the code above to mutiple lines and set a breakpoint, then check the error was cause by which object.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, September 17, 2014 5:59 AM
    Moderator
  • Fei. One thing is clear. There is some strange behavior happens with the With block.

    See the following code:

    Sub Greenstar()
    Dim myobj As New powerpoint.Application
    Dim temp As Variant
    DoCmd.OutputTo acOutputReport, "Greenstar", acFormatXLS, CurrentProject.Path & "\Greenstar.xls"
    Excel.Application.Visible = True
    Workbooks.Open (CurrentProject.Path & "\Greenstar.xls")
    myobj.Visible = msoCTrue
    ReDim temp(5)
    myobj.Presentations.Open (CurrentProject.Path & "\Star.pptx")
    myobj.ActivePresentation.Slides(1).Shapes.SelectAll
    For Each cell In Range(Rows(2), Rows(Cells.SpecialCells(xlCellTypeLastCell).Row)).Cells.SpecialCells(xlCellTypeConstants).Cells
       Select Case cell.Column
            Case 1
                Set temp(0) = myobj.Presentations.Add
            Case 2
                'ReDim Preserve temp(1)
                Set temp(1) = temp(0).Slides.Add(temp(0).Slides.Count + 1, ppLayoutBlank)
            Case 3
                'ReDim Preserve temp(2) 'Country_Code
                If temp(1).Shapes.Count = 0 Then Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 15, 100, 10) Else Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height, 100, 10)
                temp(2).TextFrame.TextRange.Text = cell.value
            Case 4
                'ReDim Preserve temp(3) 'Process_Name
                If temp(1).Shapes.Count = 2 Or cell.Offset(-1, -1).value <> "" Then Set temp(3) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(2).Top + temp(2).Height, 200, 10) Else Set temp(3) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, temp(3).Left + temp(3).Width, temp(2).Top + temp(2).Height, 200, 10)
                temp(3).TextFrame.TextRange.Text = cell.value
            Case 5
                Select Case cell.Offset(0, 1)
                       Case "Black"
                            myobj.Presentations("Star").Slides(1).Shapes(CStr(Day(cell.value))).Fill.ForeColor.RGB = vbBlack
                        Case "Red"
                            myobj.Presentations("Star").Slides(1).Shapes(CStr(Day(cell.value))).Fill.ForeColor.RGB = vbRed
                End Select
                If cell.Offset(1, 0) = "" Then
                    myobj.Presentations("Star").Windows(1).Selection.Copy
                    For i = 1 To 31
                        myobj.Presentations("Star").Slides(1).Shapes(CStr(i)).Fill.ForeColor.RGB = vbGreen
                    Next i
                    temp(1).Shapes.PasteSpecial (ppPasteBitmap)
                    Set temp(4) = temp(1).Shapes(temp(1).Shapes.Count)
                    temp(4).Height = 101.3473
                    temp(4).Width = 114.3279
                    temp(4).Left = temp(3).Left
                    temp(4).Top = temp(3).Top + temp(3).Height
                End If
            End Select
    Next cell
    End Sub

    When I tried to Give a with block for the Left, Top, Height and Width for the temp(4). I am getting on object not defined error when I try to access the temp(4)'s property again. i.e exactly here. Why so?

    Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height, 100, 10)

    But no problem comes when I set the properties line by line like I have given above. Why so?

    Wednesday, September 17, 2014 9:09 AM
  • Hi,

    >>When I tried to Give a with block for the Left, Top, Height and Width for the temp(4). I am getting on object not defined error when I try to access the temp(4)'s property again. i.e exactly here. Why so?

    Set temp(2) = temp(1).Shapes.AddTextbox(msoTextOrientationHorizontal, 10, temp(4).Top + temp(4).Height, 100, 10)<<

    I failed to reproduce the issue, the with statements works well for me. Here are the steps:
    1. create a blank presentation

    2.create a blank slide without shapes

    3. add a share like figure below:

    4. add a module with the code below:

    Sub testWithStatement()
    Dim temp As Variant
    ReDim temp(5)
    Set temp(4) = ActivePresentation.Slides(1).Shapes(1)
    With temp(4)
    .Top = .Top + 50
    .Left = .Left + 50
    End With
    End Sub

    The code works well for me. Would you mind reducing inrelevant code and share the detail steps to help us reproduce the issue?

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, September 18, 2014 8:22 AM
    Moderator