PDA

View Full Version : Solved: Object Required Error



Sirius Black
08-07-2006, 02:08 PM
Can't figure this out for the life of me. Why am I getting an Object Require Error with the following code:



Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Set Period1 = WsAggChrt.Range("M2")
Set Period2 = WsAggChrt.Range("M3")
Set Period3 = WsAggChrt.Range("M4")


The error is generated with Period3. Isn't the object already set with the line Set WbAgg = ThisWorkbook?

Thanks,

Sirius

mdmackillop
08-07-2006, 02:17 PM
Tests OK with me. Do you have any merged cells?

Sirius Black
08-07-2006, 02:30 PM
No merged cells. It's that one cell, and for some reason VBA doesn't like my code. Not sure if this will help, but here is the code in its entirety.



Sub TextBox()

Dim WbAgg As Workbook, WsAggChrt As Worksheet
Dim Period1, Period2, Period3 As String
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSld As PowerPoint.Slide
Dim Sld As Slide
Dim TxtRng As TextRange
Dim foundText As TextRange
Dim ReplacePer1, ReplacePer2, ReplacePer3 As TextRange

Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Set Period1 = WsAggChrt.Range("M2")
Set Period2 = WsAggChrt.Range("M3")
Set Period3 = WsAggChrt.Range("M4")
Set PPTApp = GetObject(, "PowerPoint.Application")
Set PPTPres = PPTApp.ActivePresentation
PPTApp.ActiveWindow.ViewType = ppViewSlide
Set ReplacePer1 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)
Set ReplacePer2 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(3).Words(Start:=3, Length:=6)
Set ReplacePer3 = PPTPres.Slides(150).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)

'Automated Footer - 2004 Period Replacement
For Each Sld In PPTPres.Slides
For Each Shp In Sld.Shapes
If Shp.HasTextFrame Then
Set TxtRng = Shp.TextFrame.TextRange
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
Do While Not (foundText Is Nothing)
Set TxtRng = TxtRng.Characters(foundText.Start + foundText.Length, TxtRng.Length)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
Loop
End If
Next
Next
End Sub



Thanks,

Sirius

mdmackillop
08-07-2006, 02:42 PM
I think your problem is here

Dim Period1, Period2, Period3 As String


This is dimming only Period3 as string, the others are Dimmed as variant. Each item requires a specific "As" to Dim it to a type.
"Set" is used to set the variable to a Range etc. If you just require the value, omit the Set.
Try the following

Option Explicit
Sub TextBox()
Dim WbAgg As Workbook, WsAggChrt As Worksheet
Dim Period1 As String, Period2 As String, Period3 As String
Dim PPTApp As PowerPoint.Application
Dim PPTPres As PowerPoint.Presentation
Dim PPTSld As PowerPoint.Slide
Dim Sld As Slide
Dim TxtRng As TextRange
Dim foundText As TextRange
Dim ReplacePer1 As TextRange, ReplacePer2 As TextRange, ReplacePer3 As TextRange
Dim Shp
Set WbAgg = ThisWorkbook
Set WsAggChrt = WbAgg.Sheets("Chart Data")
Period1 = WsAggChrt.Range("M2")
Period2 = WsAggChrt.Range("M3")
Period3 = WsAggChrt.Range("M4")
Set PPTApp = GetObject(, "PowerPoint.Application")
Set PPTPres = PPTApp.ActivePresentation
PPTApp.ActiveWindow.ViewType = ppViewSlide
Set ReplacePer1 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)
Set ReplacePer2 = PPTPres.Slides(147).Shapes(3).TextFrame.TextRange.Lines(3).Words(Start:=3, Length:=6)
Set ReplacePer3 = PPTPres.Slides(150).Shapes(3).TextFrame.TextRange.Lines(2).Words(Start:=3, Length:=6)

'Automated Footer - 2004 Period Replacement
For Each Sld In PPTPres.Slides
For Each Shp In Sld.Shapes
If Shp.HasTextFrame Then
Set TxtRng = Shp.TextFrame.TextRange
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
Do While Not (foundText Is Nothing)
Set TxtRng = TxtRng.Characters(foundText.Start + foundText.Length, TxtRng.Length)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer1, Replacewhat:=Period1)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer2, Replacewhat:=Period2)
Set foundText = TxtRng.Replace(FindWhat:=ReplacePer3, Replacewhat:=Period3)
Loop
End If
Next
Next
End Sub

Sirius Black
08-09-2006, 06:17 AM
When I dim Period1, Period2 & Period3 as String, I still get an Object Required error, this time pointing to my Set Period1 statement. I changed the dim from string to variant and the code worked without any problems.

Thanks for all of your help!

Sirius

mdmackillop
08-09-2006, 10:27 AM
Glad to help.
Make sure that you use Option Explicit, which can help identify this type of error.