Solved: Using Subroutine to efficiently call multiple entries from building blocks.dotx

[bstephens]

Ok, so I think this is probably a basic question, but struggling to figure it out, I am assuming it has to do with passing variables.

I got the following code from Graham Mayor's website (as seen here: http://www.gmayor.com/word_vba_examples.htm) and it works great to insert a "Building Block" I created called "Front of Contract 1"

Sub InsertMyBuildingBlock()
Dim strText As String
Dim oTemplate As Template
Dim oAddin As AddIn
Dim bFound As Boolean
Dim i As Long

'Define the required building block entry
strText = "Front of Contract 1"

'Set the found flag default to False
bFound = False

'If the entry has still not been found
'finally check the Building Blocks.dotx template
If bFound = False Then

Templates.LoadBuildingBlocks
For Each oTemplate In Templates
If oTemplate.Name = "Building Blocks.dotx" Then Exit For
Next
For i = 1 To Templates(oTemplate.FullName).BuildingBlockEntries.Count
If Templates(oTemplate.FullName).BuildingBlockEntries(i).Name = strText Then
Templates(oTemplate.FullName).BuildingBlockEntries(strText).Insert _
Where:=Selection.Range
'set the found flag to true
bFound = True
'Clean up and stop looking
Set oTemplate = Nothing
Exit Sub
End If
Next i
End If

'All sources have been checked and the entry is still not found
If bFound = False Then 'so tell the user.
MsgBox "Entry not found, please reinstall Building Block.dotx or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strText & Chr(146)
End If
End Sub

My question is how do I use a subroutine (is that the right term?) to efficiently call InsertMyBuildingBlock except that it will insert "Front of Contract 2" without rewriting all that code?

Asked (I hope) more clearly, how do I create an efficient function that will reuse the code in InsertMyBuildingBlock, except use a different "strText"?

I appreciate all the input I have received from you guys thus far!

Best,
Brian

[fumei]

Indeed, you pass it in as a paramter, like so:
Option Explicit

Sub InsertMyBuildingBlock(strBB_Name As String)
Dim oTemplate As Template
Dim oAddin As AddIn
Dim bFound As Boolean
Dim i As Long

'Set the found flag default to False
bFound = False

'If the entry has still not been found
'finally check the Building Blocks.dotx template
If bFound = False Then

Templates.LoadBuildingBlocks
For Each oTemplate In Templates
If oTemplate.Name = "Building Blocks.dotx" Then Exit For
Next
For i = 1 To Templates(oTemplate.FullName).BuildingBlockEntries.Count
If Templates(oTemplate.FullName).BuildingBlockEntries(i).Name = strBB_Name Then
Templates(oTemplate.FullName).BuildingBlockEntries(strBB_Name).Insert _
Where:=Selection.Range
'set the found flag to true
bFound = True
'Clean up and stop looking
Set oTemplate = Nothing
Exit Sub
End If
Next i
End If

'All sources have been checked and the entry is still not found
If bFound = False Then 'so tell the user.
MsgBox "Entry not found, please reinstall Building Block.dotx " & _
"or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strText & Chr(146)
End If
End Sub

Sub UseTheAboveDemo()
Call InsertMyBuildingBlock("Front of Contract 2")
End Sub


' Or..............

Sub UseItAnotherWay()
Call InsertMyBuildingBlock("Front of Contract " & _
InputBox("Enter ONLY number of contract front chunk. " & _
"E.g. 1"))
End Sub
In the last example of Call, it is still the same input paramter:
InsertMyBuildingBlock(strBB_Name As String)
but what is passed as that parameter is constructed.

"Front of Contract "

plus the returned value of an Inputbox asking for the user to type in only a number.

They type 2, the result: "Front of Contract 2" is passed as the input parameter to InsertMyBuildingBlock.

[fumei]

Or.....if you have a bunch:

Front of Contract 1
Front of Contract 2
Front of Contract 3
Front of Contract 4

You could do all of them together (but sequentially of course) by:
Sub Yadda()
Dim ThoseChunks() As String
Dim j As Long

ThoseChunks = Split("1,2,3,4", ",")
For j = 0 To Ubound(ThoseChunks())
Call InsertMyBuildingBlock("Front of Contract " & ThoseChunks(j))
Next
End Sub
This would action things as:

InsertMyBuildingBlock("Front of Contract 1")
InsertMyBuildingBlock("Front of Contract 2")
InsertMyBuildingBlock("Front of Contract 3")
InsertMyBuildingBlock("Front of Contract 4")

[fumei]

Or.....if you have a bunch:

Front of Contract 1
Front of Contract 2
Front of Contract 3
Front of Contract 4

You could do all of them together (but sequentially of course) by:
Sub Yadda()
Dim ThoseChunks() As String
Dim j As Long

ThoseChunks = Split("1,2,3,4", ",")
For j = 0 To Ubound(ThoseChunks())
Call InsertMyBuildingBlock("Front of Contract " & ThoseChunks(j))
Next
End Sub
This would action things as:

InsertMyBuildingBlock("Front of Contract 1")
InsertMyBuildingBlock("Front of Contract 2")
InsertMyBuildingBlock("Front of Contract 3")
InsertMyBuildingBlock("Front of Contract 4")

[bstephens]

Thanks guys! I knew it must be something simple (wish I had more background in this stuff...) Your solution works beautifully, please note there was something weird about the message box in the code as posted above, I had to revise it as follows and now it works perfect!

Option Explicit

Sub InsertMyBuildingBlock(strBB_Name As String)
Dim oTemplate As Template
Dim oAddin As AddIn
Dim bFound As Boolean
Dim i As Long

'Set the found flag default to False
bFound = False

'If the entry has still not been found
'finally check the Building Blocks.dotx template
If bFound = False Then

Templates.LoadBuildingBlocks
For Each oTemplate In Templates
If oTemplate.Name = "Building Blocks.dotx" Then Exit For
Next
For i = 1 To Templates(oTemplate.FullName).BuildingBlockEntries.Count
If Templates(oTemplate.FullName).BuildingBlockEntries(i).Name = strBB_Name Then
Templates(oTemplate.FullName).BuildingBlockEntries(strBB_Name).Insert _
Where:=Selection.Range
'set the found flag to true
bFound = True
'Clean up and stop looking
Set oTemplate = Nothing
Exit Sub
End If
Next i
End If

'All sources have been checked and the entry is still not found
If bFound = False Then 'so tell the user.
MsgBox "Entry not found"
End If
End Sub

Sub InsertMyBuildingBlock_BB1()
Call InsertMyBuildingBlock("Counterparts (CEB)")
End Sub

[fumei]

Ah, I missed changing one of your strText variables into strBB_Name.
MsgBox "Entry not found, please reinstall Building Block.dotx " & _
"or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strText & Chr(146)

should be:[vba]

MsgBox "Entry not found, please reinstall Building Block.dotx " & _
"or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strBB_Name & Chr(146)

BTW: just as a suggestion that will help you (and us)...

"please note there was something weird about the message box in the code as posted above,"

is not great. Something weird is not descriptive. You probably got a

Compile error;

variable not defined

Yes? It will make it MUCH easier for you if, when mentioning errors, to state the error. "Weird" is just weird.

This is not in any way meant to be critical, it really will make things easier in the long run if you always try to be be clear and concise.

[bstephens]

Thanks, you're right, will do!

[gmaxey]

Rather than checking every buildingblock name in a template against the passed parameter, it might be better to try to set a buildingblock object to the the desired name and use error handling:

Sub InsertMyBuildingBlock(strBB_Name As String)
Dim oTemplate As Template
Dim bFound As Boolean
Dim oBB As BuildingBlock
bFound = False
Templates.LoadBuildingBlocks
For Each oTemplate In Templates
On Error Resume Next
Set oBB = oTemplate.BuildingBlockEntries(strBB_Name)
On Error GoTo 0
If Not oBB Is Nothing Then
bFound = True
oTemplate.BuildingBlockEntries(strBB_Name).Insert Where:=Selection.Range
Exit Sub
End If
Next oTemplate
If Not bFound = True Then
MsgBox "Entry not found, please reinstall Building Block.dotx " & _
"or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strBB_Name & Chr(146)
End If
End Sub

[gmaxey]

Actually this might be a bit more streamlined:



Sub InsertMyBuildingBlock(strBB_Name As String)
Dim oTemplate As Template
Dim oBB As BuildingBlock
Templates.LoadBuildingBlocks
For Each oTemplate In Templates
On Error Resume Next
Set oBB = oTemplate.BuildingBlockEntries(strBB_Name)
On Error Goto 0
If Not oBB Is Nothing Then
oTemplate.BuildingBlockEntries(strBB_Name).Insert Where:=Selection.Range
End If
Next oTemplate
MsgBox "Entry not found, please reinstall Building Block.dotx " & _
"or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strBB_Name & Chr(146)
End Sub

[gmaxey]

Nope I left out the Exit Sub



Sub InsertMyBuildingBlock(strBB_Name As String)
Dim oTemplate As Template
Dim oBB As BuildingBlock
Templates.LoadBuildingBlocks
For Each oTemplate In Templates
On Error Resume Next
Set oBB = oTemplate.BuildingBlockEntries(strBB_Name)
On Error Goto 0
If Not oBB Is Nothing Then
oTemplate.BuildingBlockEntries(strBB_Name).Insert Where:=Selection.Range
Exit Sub
End If
Next oTemplate
MsgBox "Entry not found, please reinstall Building Block.dotx " & _
"or check with computer administrator", vbInformation, "Building Block " _
& Chr(145) & strBB_Name & Chr(146)
End Sub

[bstephens]

Thanks Greg, that works perfectly. My code is looking far, far better now!

Best,
Brian

[fumei]

Agreed. Generally speaking it is always better to use objects rather than brute force processing.

Brian are you following why Greg's suggestion is better?