|
|
The information in this website is provided without risk or
obligation and free of charge. However, if you have benefitted
from my efforts here and would like to make a contribution to
help me continue and maintain this work then any donation will
be greatly appreciated. Please click the adjacent button to
access PayPal. Thank you.
|
|
The purpose of
this Microsoft Word Tips & Microsoft Word Help page is to document and explain a
VBA process for extracting individual numbers from a user input
string of continuous, discontinuous, or grouped continuous numbers.
For example, take the string "2, 6, 8-12, 15." This string contains
the individual numbers 2, 6, 8, 9, 10, 11, 12 and 15. Not long ago I came
across a post in a Microsoft Word VBA support forum which asked how to
identify and process information in certain rows in a Word table.
The process for a fixed set of rows is not very difficult.
The following example illustrates the basic code to process the set of rows
indexed 2, 6, 8-12, or 15 in a selected Word table. |
Sub Demo1()
Dim i As Long
Dim oRow As Row
For i = 1 To Selection.Tables(1).Rows.Count
Select Case i
Case 1, 6, 8 To 12, 15
Set oRow =
Selection.Tables(1).Rows(i)
'Code to process row
End Select
Next i
Set oRow = Nothing
End Sub |
|
Unfortunately the code above is a fixed solution. It processes
rows 2, 6, 8-12, and 15 extremely efficiently. However, without constant changes to the written
code a user can not change the set of rows to process. A dynamic
solution requires a means of extracting the numerical values from a user
input string. It is not that difficult to extract continuous or
discontinuous numbers from a user input string either (e.g., 2, 6, 8, 9, 10,
11, 12, 15). The following
example illustrates one possible method: |
Sub Demo2()
Dim arrNumbers() As String
Dim i As Long
Dim oRow As Row
arrNumbers = Split(InputBox("Enter the rows to process separated by a
comma", "User Input"), ",")
For i = 0 To UBound(arrNumbers)
Set oRow = Selection.Tables(1).Rows(arrNumbers(i))
'Code to process row
Next i
Set oRow = Nothing
End Sub |
|
Using the code in Demo2. The user would provide the input shown below to
process rows 2, 6, 8-12, and 15. |
|
|
|
While this will certainly work, users may soon grow weary with the basic
dynamic method if their requirements involved frequent or long strings of
continuous numbers. Consider the user input required to process rows
1, 5, 10 through 500 and 505! |
|
Extracting continuous, discontinuous, and "grouped continuous" numbers
(e.g., 2-6-8-12, 15 or 1, 5, 10-500, 505) from a user input string is when
the process becomes very interesting and considerably more complex. In
looking for a solution I came across some code for manipulating string data
for this purpose prepared by MVP Cindy Meister. With Cindy's
permission I'll call this code a "jewel in the rough" and through
collaboration Cindy and I have refined it to the state presented here. |
|
As in the basic dynamic method, the process involves creating an array
containing each of the individual numbers in the user input string as a
subscript element. This includes each continuous and discontinuous
number in the user input string as well as the individual numbers contained
in the continuous groups. A static demonstration of this process using
the user input string "2, 6, 8-12, 15" is shown below: |
Sub Demo3()
Dim pInput As String
Dim arrNumbers() As String
Dim i As Long
Dim j As Long
i = 0
ReDim Preserve arrNumbers(i)
pInput = "2, 6, 8-12, 15"
arrNumbers(i) = "2"
i = i + 1
ReDim Preserve arrNumbers(i)
arrNumbers(i) = "6"
i = i + 1
For j = 8 To 12
ReDim Preserve arrNumbers(i)
arrNumbers(i) = j
i = i + 1
Next j
ReDim Preserve arrNumbers(i)
arrNumbers(i) = "15"
For i = 0 To UBound(arrNumbers)
MsgBox "Processing row " & arrNumbers(i)
Next i
End Sub |
|
In the static example we know the numbers involved, the arrangement of those numbers
individually and in groups, and the
group starting and ending numbers. Writing the code to add those
number to the array was elementary.
In a dynamic user input all of those things must be determined and
processed at run-time. The user input string must be evaluated,
broken down, and processed in a logical sequence using methods for string
manipulation. A comprehensive solution involves both the logic process
and a user interface. This solution with explanatory comments is
provided below: |
|
|
|
|
|
|
|
You can download a file containing the code shown above
here.
There you have it! With minor
changes the procedure ProcessRows can be adapted to process almost any
object. Process tables, process paragraphs, print specific pages, etc. |
|
A special thanks to Cindy Meister. This page would not have been
possible without her assistance!
|
|
See:
Installing Macros for instructions on how to set up and use the macro
listed above.
See:
Setup Menu/Toolbar for instructions on how to run a macro from a menu or
toolbar icon.
See:
Keyboard Shortcut for instructions on how to run a macro using a
keyboard shortcut. |
|
|
Looking for something else?
|
|
