Please help me identify potential user errors with my coding - WORD VBA 2010

[pk247]

Hi Everyone,

I hope you could possibly help me with this piece of code I've written.

It works well for me but I'd like to share it out with my work colleagues and I'd really appreciate it if someone could help me spot any potential issues I might face in sharing this. Basically we tag requirements in a large Word doc and anything custom needs a "-C-" tag. So rather than trying to remember what number to enter each time in the Word doc the user can enter [REQ-C-X] then once fully tagged the user can run the code and the numbers will update. It needs the functionality to enter the number to start from because we sometimes have to update old requirements docs.

Hope this makes sense and if you can spot any flaws please let me know. This code is really helpful but it's just not fool-proof :( yet...


Sub TagIncrementer()

Dim iCount As Long, strSearchTag As String, strTagCount As Integer

strSearchTag = UCase(InputBox("Enter Tag to Increment" & vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", "Tag Increment Tool", ""))
strTagCount = UCase(InputBox("Enter Number to Start Tag Increment", "Tag Increment Tool", ""))

iCount = strTagCount - 1
With ActiveDocument.Content
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.text = strSearchTag & "-C-X"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With

If .Find.found = True Then
While .Find.found = True
iCount = iCount + 1
.text = strSearchTag & "-C-" & Format(iCount, "")
.Collapse wdCollapseEnd
.Find.Execute
Wend
MsgBox "Tags incremented to " & iCount
Exit Sub

Else

While .Find.found = False
MsgBox "Tag not found!" & vbCrLf & vbCrLf & "Please Check and Try Again - Case Sensitive"
Exit Sub
Wend

End If

End With

End Sub

Thanks so much!

Paul, Ireland :beerchug:

[SamT]

Check this out and see what you think. Bracketed ''''comments'''' are my notes to you, remove them after understanding. Other comments explain the code's purpose.
Option Explicit

Sub TagIncrementer()

Dim lCount As Long, strSearchTag As String, vTagCount As Variant

'Convert input Box input to UCase
strSearchTag = UCase(InputBox("Enter Tag to Increment" _
& vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", _
"Tag Increment Tool", ""))

GetTagNumber:
vTagCount = InputBox("Enter Number to Start Tag Increment", _
"Tag Increment Tool", "") ''''Numbers don't have U & L Case''''

'If they did not enter a value
If vTagCount = vbNullString Then vTagCount = 1

'If typo'ed a character
If Not IsNumeric(vTagCount) Then
MsgBox "Please enter Numbers Only"
GoTo GetTagNumber
End If

lCount = vTagCount

TryingAgain:
With ActiveDocument.Content
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = strSearchTag & "-C-X"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With

If Not .Find.found = True Then
MsgBox "Tag not found!" & vbCrLf & vbCrLf & "Please Check and Try Again" ''''- Case Sensitive" <-- all tags in Document must be UCase''''
strSearchTag = UCase(InputBox("Enter Tag to Increment" _
& vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", _
"Tag Increment Tool", ""))
GoTo TryingAgain

Else
While .Find.found = True
.Text = strSearchTag & "-C-" & lCount
.Collapse wdCollapseEnd
.Find.Execute
lCount = lCount + 1
Wend
MsgBox "Tags incremented to " & lCount - 1
Exit Sub
End If
End With
End Sub

[pk247]

Really nice SamT :clap:

I was totally blind in not seeing the number format in the inputbox. I'm still quite a novice to vba but this works/flows really well. The only thing that I noticed was that if I hit cancel I end up in a loop. Is there a way to make the cancel exit the sub?

Many thanks for the quick reply and the help!

Paul, Ireland :beerchug:

[pk247]

I tried this myself and it works just fine. I just added in a few if=blanks and it cancels out just fine. Thanks again SamT for your gudance!

PS The
GoTo TryingAgain - very handy tip for the future - thanks!


Option Explicit

Sub TagIncrementer()

Dim lCount As Long, strSearchTag As String, vTagCount As Variant

'Convert input Box input to UCase
strSearchTag = UCase(InputBox("Enter Tag to Increment" _
& vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", _
"Tag Increment Tool", ""))
If strSearchTag = "" Then Exit Sub

GetTagNumber:
vTagCount = InputBox("Enter Number to Start Tag Increment", _
"Tag Increment Tool", "") ''''Numbers don't have U & L Case''''
If vTagCount = "" Then Exit Sub

'If they did not enter a value
If vTagCount = vbNullString Then vTagCount = 1

'If typo'ed a character
If Not IsNumeric(vTagCount) Then
MsgBox "Please enter Numbers Only"
GoTo GetTagNumber
End If

lCount = vTagCount

TryingAgain:
With ActiveDocument.Content
With .Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = strSearchTag & "-C-X"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
.Execute
End With

If Not .Find.Found = True Then
MsgBox "Tag not found!" & vbCrLf & vbCrLf & "Please Check and Try Again" ''''- Case Sensitive" <-- all tags in Document must be UCase''''
strSearchTag = UCase(InputBox("Enter Tag to Increment" _
& vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", _
"Tag Increment Tool", ""))
If strSearchTag = "" Then Exit Sub
GoTo TryingAgain

Else
While .Find.Found = True
.Text = strSearchTag & "-C-" & lCount
.Collapse wdCollapseEnd
.Find.Execute
lCount = lCount + 1
Wend
MsgBox "Tags incremented to " & lCount - 1
Exit Sub
End If
End With
End Sub

Paul, Ireland :beerchug:

[gmaxey]

Certainly nothing wrong with Sam's method. I just like to avoid "GoTo" statements. I propose:


Sub TagIncrementer()
Dim lngCount As Long, strSearchTag As String, vTagCount As Variant
Dim oRng As Word.Range, bFound As Boolean
Do
bFound = False
Do
strSearchTag = InputBox("Enter Tag to Increment" _
& vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", "Tag Increment Tool")
If StrPtr(strSearchTag) = 0 Then Exit Sub 'Because user canceled.
strSearchTag = UCase(strSearchTag)
Loop Until Not strSearchTag = vbNullString
Do
vTagCount = InputBox("Enter Number to Start Tag Increment", "Tag Increment Tool", "1")
If StrPtr(vTagCount) = 0 Then Exit Sub 'Because user canceled.
Loop Until IsNumeric(vTagCount)
lngCount = vTagCount
Set oRng = ActiveDocument.Range
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = strSearchTag & "-C-X"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchWildcards = True
While .Execute
bFound = True
oRng.Text = strSearchTag & "-C-" & lngCount
oRng.Collapse wdCollapseEnd
lngCount = lngCount + 1
Wend
If bFound Then
MsgBox "Tags incremented to " & lngCount - 1
Exit Do
End If
End With
MsgBox "Tag not found!" & vbCrLf & vbCrLf & "Please Check and Try Again"
Loop
lbl_Exit:
Exit Sub
End Sub

[pk247]

I am sorry about this! Is there a way to NOT make the strSearchTag case-sensitive? Reason being I can see a user tagging like [Req-C-x] and so when they type "REQ" in the inputbox it will not return any increments.

Can anyone please help with this (hopefully small) edit to the code?

Paul, Ireland :beerchug:

[pk247]

Thank you Greg! It's very useful to see another way to make the code more efficient. It runs perfectly too :)

I'm learning so much from this forum - and you and SamT have been very helpful indeed. Thanks again. Just this small issue about case-sensitive and it'll be fool-proof :)

[gmaxey]

Sub TagIncrementer()
Dim lngCount As Long, strSearchTag As String, vTagCount As Variant
Dim oRng As Word.Range, bFound As Boolean
Do
bFound = False
Do
strSearchTag = InputBox("Enter Tag to Increment" _
& vbCrLf & vbCrLf & "CAUTION: ALL Tags will be Incremented!", "Tag Increment Tool")
If StrPtr(strSearchTag) = 0 Then Exit Sub 'Because user canceled.
'strSearchTag = UCase(strSearchTag)
Loop Until Not strSearchTag = vbNullString
Do
vTagCount = InputBox("Enter Number to Start Tag Increment", "Tag Increment Tool", "1")
If StrPtr(vTagCount) = 0 Then Exit Sub 'Because user canceled.
Loop Until IsNumeric(vTagCount)
lngCount = vTagCount
Set oRng = ActiveDocument.Range
Selection.Find.MatchCase = False 'Added
With oRng.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = strSearchTag & "-C-x"
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False 'Added
'.MatchWildcards = True 'stetted out. Don't know why it was there in the first place.
While .Execute
bFound = True
oRng.Text = strSearchTag & "-C-" & lngCount
oRng.Collapse wdCollapseEnd
lngCount = lngCount + 1
Wend
If bFound Then
MsgBox "Tags incremented to " & lngCount - 1
Exit Do
End If
End With
MsgBox "Tag not found!" & vbCrLf & vbCrLf & "Please Check and Try Again"
Loop
lbl_Exit:
Exit Sub
End Sub

[pk247]

PERFECT!

Thanks so much Greg for that quick update. I've got a lot to learn...

Cheers!

Paul, Ireland :beerchug:

[gmaxey]

You're welcome and you're not alone in the lot to learn club.

[SamT]

@ Greg, I'm still using VBA 2002 and the help file on InputBox Says
If the user clicks Cancel, the function returns a zero-length string ("").How does that jive with
If StrPtr(strSearchTag) = 0 Then
Exit Sub 'Because user canceled.
Loop Until Not strSearchTag = vbNullString in VBA =>2007?

I had set my code up to allow the User to just hit enter if they wanted to start incrementing tag numbers at the default of 1 (one). Of course I had not considered the User pressing Cancel. :(

I see that you are using a Do... (Exit Do)... Loop to avoid using Goto's. Interesting.

[gmaxey]

SamT,

I have no formal training in this stuff. The little I know is trial and error or monkey see, monkey do. From what I have read, StrPtr is an undocumented VBA function that returns the memory location of the data variable apparently a zero length string has no memory location and hence returns 0 while a vbNullString does and returns a value other than 0.


Sub ScratchMacro()
'A basic Word macro coded by Greg Maxey
Dim strTest As String
Do
strTest = InputBox("Enter something, don't enter something, or click Cancel")
If StrPtr(strTest) = 0 Then
Exit Sub 'Because user canceled.
Else
Debug.Print StrPtr(strTest)
End If
Loop Until Not strTest = vbNullString
lbl_Exit:
Exit Sub
End Sub



Since StrPtr is undocumented it may not work next week, next month, next century :-(. Unfortunately, unlike Excel, the Word OM doesn't have an InputBox method. If it did the better way would be:




Public Sub Excel_andOtherAppsWithInputBoxMethod()
Dim varTest
varTest = Application.InputBox("Enter something, don't enter something, or click Cancel")
If varTest = False Then Exit Sub
MsgBox (varTest)
End Sub

[SamT]

The value of vbNullString is "", an empty string.

I am very leery of undocumented features, their use has caused many programs to stop working when the "feature" was taken away. Plus, since they are undocumented, very few people can even recognise their use and won't be able to maintain the code.

But... The only (bad UI) alternatives I can see are to offer yet another dialog to ask if they want to cancel, or auto cancel if both inputs are null
If strSearchTag & vCount = vbNullString Then Exit Sub

From what you say about Word's InputBox, the only "proper" way would be to use a UserForm.

[gmaxey]

Sam,

I understand your aversion to undocumented processes but here no certainty in anything. In Word 2010 you could create and use building blocks with comments. In Word 2013/2016 you can't. This broke several projects. Up until Word 2016, right clicking a misspelled word triggered the Selection Change event. Not so anymore with Word 216. A coupe more projects are broken.

Yes a userform would be the best way I suppose. I wonder though why Excel has a .Inputbox method while Word doesn't.

[SamT]

Excel and VBA were written fresh with Excel 5 and both were overseen by the same PM. (Joel on Software) (http://www.joelonsoftware.com/)

I believe that the Word back end was adapted from WordStar. :dunno

With office 7, a New School of thought took over where Cool was the name of the game rather than stodgy old backwards compatibility.

[gmaxey]

Yes, and too bad.

[Paul_Hossler]

1. I think MS has documented StrPtr finally (for 2010 and beyond) when they released 64 bit Office

https://msdn.microsoft.com/en-us/library/office/ee691831(v=office.14).aspx


2. They is a subtle difference between S = "" and S = vbNullString, but I've only ever found that it makes much of a difference in Excel

http://www.aivosto.com/vbtips/stringopt.html#whyslow

3. Word has a VBA-level InputBox, but not an Application.InputBox like Excel. Usually I can get by with that



Sub test()
Dim s As String

s = InputBox("Enter a String", "Demo", "Your String")

If Len(s) > 0 Then
MsgBox "You entered " & s
Else
MsgBox "You cancelled"
End If

End Sub

[gmaxey]

Paul,

Thanks for the link on StrPtr. I'm aware that VBA has an Inputbox function. The problem with your method is entering nothing is not really the same as canceling.


Sub test()
Dim s As String

s = InputBox("Enter a String", "Demo", "Your String")

If Len(s) > 0 Then
MsgBox "You entered " & s
Else
MsgBox "You cancelled, or maybe you were just being difficult and deleled the default input and " _
& "clicked enter without entering anything." & vbCr + vbCr _
& "Without an application input box method the only way to catch you in this deed is to use StrPtr."
End If

End Sub

[Paul_Hossler]

1. You're welcome (StrPtr)

2. I figured you knew InputBox, but thanks for pointing out [Cancel] also returns a null string. The few times I've used InputBox in Word, I didn't need to tell the responses apart, but it's good to know.