Error inserting data at bookmark

[John G.]

Putting together an entertainment agreement doc. Using a userform for the
varible information that I have bookmarked in the doc. I'll admit I'm a
novice at VBA so any help is appreciated.
I'm getting a "Compiler Error: ByRef argument type mismatch" error when I go
to run the UpdateBookmark sub (I got this from the MVP FAQ by Dave Rado).
The sub worked OK when I used the simple formated date value in a
"mm/dd/yyyy" format. But I need a ordinal day format "17th day of August
2009" which I produce in the calendar sub.
I'm using linked textboxes to flow the contractor info (reason for
concatenating), the services, and the compensation info in the doc. The
bookmarks are in the first textbox of each linked group.

I'm loading the doc with data when the "cmbFinish" button is clicked to give
the user the ability to review the info on the userform before the transfer.

Thx in advance. John G

-------------------------------
Public ContrInfoCombo, FormalDt, OrdinalDay As String

Private Sub cmbDate_Click()

With Calendar1
.Value = Date
.Visible = True
End With
Repaint

End Sub

Private Sub Calendar1_Click()

cmbDate.Visible = False
tbAgreeDt.Value = Format(Calendar1.Value, "mm/dd/yy")
Calendar1.Visible = False
lblLine.Visible = True
Select Case Day(tbAgreeDt.Value)
Case 1, 21, 31
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "st"
Case 2, 22
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "nd"
Case 3, 23
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "rd"
Case Else
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "th"
End Select
FormalDt = OrdinalDay + " day of " + Format(tbAgreeDt.Value, "MMMM yyyy")

End Sub

Private Sub tbContrPh_AfterUpdate()

ContrInfoCombo = tbContrName.Value + ", " + tbContrAddr.Value + ", " + _
tbContrCSZ.Value + ", " + Format(tbContrPh.Value, "(000)000-0000")
tbContrPh.Value = Format(tbContrPh.Value, "(000)000-0000")

End Sub

Private Sub tbComp_Change()

cmbFinish.Enabled = True

End Sub

Private Sub cmbFinish_Click()

UpdateBookmark "AgreeDt", FormalDt ' <------Getting error here
UpdateBookmark "ContrInfo", ContrInfoCombo
UpdateBookmark "Services", tbServices.Value
UpdateBookmark "Comp", tbComp.Value
UpdateBookmark "Today", Format(Date, "MMM. d, yyyy")
SCD_EA_Window.Hide

End Sub

Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)

Dim BMRange As Range
Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
BMRange.Text = TextToUse
ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange

End Sub

 

[Doug Robbins - Word MVP]

You must declare FormalDt as a String variable.

Private Sub Calendar1_Click()
Dim FormalDt as String
cmbDate.Visible = False
tbAgreeDt.Value = Format(Calendar1.Value, "mm/dd/yy")
Calendar1.Visible = False
lblLine.Visible = True
Select Case Day(tbAgreeDt.Value)
Case 1, 21, 31
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "st"
Case 2, 22
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "nd"
Case 3, 23
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "rd"
Case Else
OrdinalDay = Str(Day(tbAgreeDt.Value)) + "th"
End Select
FormalDt = OrdinalDay + " day of " + Format(tbAgreeDt.Value, "MMMM
yyyy")

End Sub

You would need to do the same with each of the other variables that you are
using.

An alternative, which would avoid the error would be to modify the

Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As String)

to:

Sub UpdateBookmark(BookmarkToUpdate As String, TextToUse As Variant)

However, you should really preface your code with Option Explicit which must
appear in your code before any other source code statements.

When Option Explicit appears in a file, you must explicitly declare all
variables using the Dim or ReDim statements. If you attempt to use an
undeclared variable name, an error occurs at compile time.

Use Option Explicit to avoid incorrectly typing the name of an existing
variable or to avoid confusion in code where the scope of the variable is
not clear. If you do not use the Option Explicit statement, all undeclared
variables are of Object type.



--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[John G.]

Doug, thanks for the reply. I have a "Public" declaration which includes
FormalDT at the beginning of the code. I was of the understanding that this
would go across all procedures. Would I then again need to declare it in the
subroutine where it is used?
John G.

 

[Doug Robbins - Word MVP]

Probably not, if you specifically nominate the type of each variable.

I am not sure that your

Public ContrInfoCombo, FormalDt, OrdinalDay As String

does that.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[That Guy]

I don't see anything wrong with your declarations.

I have tested most of your code and I have not found any issue. the
only error I can get is when I supply the update bookmark function
with an invalid bookmark.

Try activating the form using F8 so that you can step through the
application and determine where exactly the error is originating. It
may not be from where you think. Even if the debugger highlights a
line as suspect that just may be where the error was discovered and
not necessarily generated.

The only other thing I would change: don't use the "+" sign to
concatenate strings, use the "&" ampersand. The plus sign can lead to
unexpected issues.

Other somewhat trivial syntactical things are the fact that you
declare only sub, you give it no scope, i.e. public or private, as
well as function deceleration is better when you use byval and byref
to delineate the scope of those parameters. byref for deep copy, byval
for shallow.

I am not sure any of that will help as I have tried your code and have
not had any issue with it.

send me an email with the form as an attachment and I will see if I
can replicate the issue. (only if that is ok with you that is)

good luck

 

[Doug Robbins - Word MVP]

The error was reproducible here with the code as posted by the OP.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[John G.]

Here's a update...if this helps. I went ahead and replaced the string
variable names with the actual routine for generating the string and now it's
working.
What I find curious is that the variable "OrdinalDay" is declared along with
"FormalDt". While the latter variable fails, the former variable works. The
variable "ContrInfoCombo" also failed but the routine worked in it's place.

Code below:
-------------------

Private Sub cmbFinish_Click()

UpdateBookmark "AgreeDt", OrdinalDay & " day of " & _
Format(tbAgreeDt.Value, "MMMM yyyy") 'FormalDt
UpdateBookmark "ContrInfo", tbContrName.Value & ", " & _
tbContrAddr.Value & ", " & tbContrCSZ.Value & ", " & _
Format(tbContrPh.Value, "(000)000-0000") 'ContrInfoCombo
UpdateBookmark "Services", tbServices.Value
UpdateBookmark "Comp", tbComp.Value
UpdateBookmark "Today", Format(Date, "MMM. d, yyyy")
SCD_EA_Window.Hide

End Sub

 

[John G.]

Your "ByVal" suggestion in the UpdateBookmark routine did the trick. I reset
the sub parameters back to the string variables and everything fell right
into place.
Thanks to both Doug Robbins and "That Guy" for the help and suggestions.