Run-time error '424': Object required

[Shaun]

Hi Everyone

I am getting an run-time error when a my function then looks at a sub.

Would any be kind enough to please take a look, I have been unable to resolve?

Cheers

Shaun

 

[Hui]

The sub knows nothing about the variables defined in the Function

I would change

addchar ' what is going here?

to:

objDoc.Characters.Last.Select
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select
'addchar ' what is going here?

Then delete the Sub AddChar

Also why are you using FnAddTables as a function?
It would be better as a Sub

 

[Shaun]

Hi Hui

When I run:

Function FnAddTables()

'Apply styling to text
objDoc.Bookmarks.Add ("ClientName1")
Clientname1 = ActiveWorkbook.Sheets("Sheet1").Range("C3")
With objDoc
  If objDoc.Bookmarks.Exists("ClientName1") Then
  .Bookmarks("ClientName1").Range.Text = Clientname1
  objWord.Selection.Style = -2
  End If
End With
  
addchar ' what is going here?

after "ClientName1" bookmark is added sub addchar() is called.

As soon as it tries to execute addchar:

Sub addchar()
objDoc.Characters.Last.Select
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select
End Sub

I get the run-time error.

Cheers

Shaun

 

[Hui]

see my comments above

 

[Shaun]

Hi Hui

Thank you for taking a look.

I need condense the script because I am getting another error...procedure too long.

These three lines of code get run many times so I thought I could pull them out of the main code to condense and hopefully fix the procedure too long error.

The files attached are not the complete code as these examples only replicate the problems. I can attach the full code if that will help?

Cheers

Shaun

 

[Hui]

The following worked all the way through for me in Excel 2010

Sub FnAddTables()

Dim arrData
Dim arrNumData
Dim cell As Range
Dim intNoOfColumns
Dim objWord
Dim objDoc
Dim ClientName As String
Dim Clientname1 As String
Dim Text As String
arrData = Range("B5:G26")
intNoOfColumns = 6

'Open tax planning template document
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Open("C:\Users\Huis\Desktop\Chandoo Example.docx") 'this will need amending accordingly
 
'Add client name to the front cover
ClientName = ActiveWorkbook.Sheets("Sheet1").Range("C3")

With objDoc
  If objDoc.Bookmarks.Exists("ClientName") Then
  .Bookmarks("ClientName").Range.Text = ClientName
  End If
End With

'Move to the end of the document
objDoc.Characters.Last.Select

'Apply styling to text
objDoc.Bookmarks.Add ("ClientName1")
Clientname1 = ActiveWorkbook.Sheets("Sheet1").Range("C3")
With objDoc
  If objDoc.Bookmarks.Exists("ClientName1") Then
  .Bookmarks("ClientName1").Range.Text = Clientname1
  objWord.Selection.Style = -2
  End If
End With
   
objDoc.Characters.Last.Select
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select
'addchar ' what is going here?

'
'Add Estimated Net Profit Before Tax table
'
  Set objRange = objDoc.Range
  objDoc.Tables.Add objRange, 1, intNoOfColumns
  Set objTable = objDoc.Tables(1)
  objTable.Borders.Enable = False
   
'Add data into table
 For i = 1 To UBound(arrData)
  If arrData(i, 1) <> "" Then
  If i > 1 Then objTable.Rows.Add
  For j = 1 To intNoOfColumns
  objTable.cell(i, j).Range.Text = arrData(i, j)
  Next
  End If
  Next

'Format style = No Spacing
objTable.Range.Style = "No Spacing"

'Right justify cells from Cell(1, 2) to (i, j)
numCell = objTable.Rows.Count
If numCell > 1 Then
  For i = 1 To numCell
  For j = 2 To intNoOfColumns
  objTable.cell(i, j).Range.ParagraphFormat.Alignment = 2
  Next
  Next
End If

'Format cells from Cell(2, 2) to (i, j) to #,##0
If numCell > 1 Then
  For i = 2 To numCell
  For j = 2 To intNoOfColumns
  objTable.cell(i, j).Range.Text = VBA.Format(Val(objTable.cell(i, j).Range.Text), "#,##0")
  Next
  Next
End If

'Format "Total" borders
If numCell > 1 Then
  For i = numCell To numCell
  For j = 2 To intNoOfColumns
  objTable.cell(i, j).Range.Borders(wdBordertop).LineStyle = wdLineStyleSingle
  objTable.cell(i, j).Range.Borders(wdBorderBottom).LineStyle = wdLineStyleDouble
  Next
  Next
End If

'Format row 1 of table "Bold"
objTable.Rows(1).Range.Font.Bold = True
objTable.Rows(objTable.Rows.Count).Range.Font.Bold = True

'Autofit column widths
objTable.Columns.AutoFit

'
'Clear out data ready for next table
'
objWord.ActiveDocument.Characters.Last.Select
objWord.Selection.Collapse
  With objRange
  .Collapse Direction:=wdCollapseEnd
  .MoveEnd
  .InsertParagraphAfter
  .Collapse Direction:=wdCollapseEnd
  End With

objDoc.Characters.Last.Select
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select

Text = ActiveWorkbook.Sheets("Sheet1").Range("K6")
objWord.Selection.TypeText (Text)
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select
'
'Bullet points start here
'
Text = ActiveWorkbook.Sheets("Sheet1").Range("K7")
objWord.Selection.TypeText (Text)
'objDoc.Selection.Range.ListFormat.ApplyListTemplateWithLevel _
  ListTemplate:=ListGalleries(wdBulletGallery).ListTemplates(1), _
  ContinuePreviousList:=False, ApplyTo:=wdListApplyToWholeList, _
  DefaultListBehavior:=wdWord10ListBehavior

objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select

Text = ActiveWorkbook.Sheets("Sheet1").Range("K8")
objWord.Selection.TypeText (Text)
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select

Text = ActiveWorkbook.Sheets("Sheet1").Range("K9")
objWord.Selection.TypeText (Text)
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select

Text = ActiveWorkbook.Sheets("Sheet1").Range("K10")
objWord.Selection.TypeText (Text)
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select

Text = ActiveWorkbook.Sheets("Sheet1").Range("K11")
objWord.Selection.TypeText (Text)
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select
'
'Bullet points end here
'
Text = ActiveWorkbook.Sheets("Sheet1").Range("K12")
objWord.Selection.TypeText (Text)
objWord.Selection.InsertParagraphAfter
objDoc.Characters.Last.Select

objWord.Visible = True
End Sub

output Word File:

 

[Shaun]

Hi Hui

Here is the code that I am working on. I hope it helps you see my problem.

The example code works for me also when:

  objdoc.Characters.Last.Select
  objWord.Selection.InsertParagraphAfter
  objdoc.Characters.Last.Select

is left in place, but when I pull these three lines out into their own sub that is when I get the run-time error message. I need to pull these three lines out in to their own sub because in the complete code the "procedure is too long" and won't run at all.

Cheers

Shaun

 

[Hui]

I understand what your trying to do

But the 2 objects, Objdoc and Objword aren't available in the Subroutine

If its working as is, why mess with it ?

 

[Shaun]

Hi Hui

I need to pull the subroutine out because as the code stands it is too long and produces the error "procedure too long".

Those three lines of code are repeated throughout the code many many times, so there is an opportunity to put them in their own subroutine, shortening the length of the main code and resolving the "procedure too long" error preventing the code from running.

How can I make objdoc and objword objects available in the subroutine?

Cheers

Shaun

 

[NARAYANK991]

Hi Shaun ,

There is absolutely no problem in using the subroutine addchar the way you have used it.

Just declare the two variables used within the subroutine as public ( the alternative is to pass them as parameters to the subroutine , but you can try that out after you have tried this method ) , outside the main procedure FnAddTables , which incidentally should be a subroutine rather than a function.

A piece of code is declared as a function so that it can be called from another piece of code , and it returns a value which is then assigned to a variable. For example , suppose you have two pieces of code named CodeA and CodeB ; suppose CodeB is declared as a function ; then the way to use it would be to have the following line of code in CodeA :

x = CodeB()

where CodeB is assumed to not have any passed parameters.

Suppose CodeB had been declared as a Sub , then the way to use it would be to have the following line of code in CodeA :

CodeB

again assuming that CodeB does not use any parameters.

Declare your two variables as follows :

Public objWord As Object
Public objDoc As Document


Function FnAddTables()
Dim arrData
Dim arrNumData
Dim cell As Range
Dim intNoOfColumns
Dim ClientName As String
Dim Clientname1 As String
Dim Text As String
arrData = Range("B5:G26")
intNoOfColumns = 6

I have shown only a portion of your Function FnAddTables , just to highlight that the declaration of the two variables used within the addchar subroutine is outside the Function.

Narayan

 

[Shaun]

Hi Narayan

Thank you for your explanation of the use of functions, that makes complete sense.

In relation to declaring the variables objWord and objDoc I will implement and test that tonight.

Cheers

Shaun