How to achieve below scenario using VBScript or VBA?

Question

I have text in a variable as below:

 4.1.6.1    Display of user roles and system versions

[4.01.070]
It must be possible for a user logged into a named XXXXXX project, 
to have all user information and all granted user roles displayed in a simple way. 
An example of the displayed information is displayed below:

User Name: XXXX
Full Name: XXXXX XXXX
e-Mail: XXXX@XXXXXX.com
Status: Active
Deactivation Date: 23 Marts 2028
Granted User Roles:     
    Test Case Author
    Requirement Author
Release manager
Description:
  Please note this user was trained in XXXXX in spring 1903.

[4.01.072]
When pressing the About button, the following information should be shown:
iAuthorize DLL version: [Major].[Minor].[Bug].[0] 
Workflow Script version:    [Major].[Minor].[Bug]
Std. Template version:  [XXXXX VX or XXXXXX VX]

The HP XXXX XXXXX version should be the name of the item called "XXXXX Version" on the XXXXX project list.

In the above text i need to find how many times a specific pattern string "[x.xx.xx]" occured.

In above case 2 times they occurred.

1st one is [4.01.070] and second one is [4.01.072]

I want ouput, something like below:

2 occurences found:

1st occurence:

Name: 4.01.070 Content:

 It must be possible for a user logged into a named XXXXXX project, 
    to have all user information and all granted user roles displayed in a simple way. 
    An example of the displayed information is displayed below:

User Name: XXXX
Full Name: XXXXX XXXX
e-Mail: XXXX@XXXXXX.com
Status: Active
Deactivation Date: 23 Marts 2028
Granted User Roles:     
    Test Case Author
    Requirement Author
Release manager
Description:
  Please note this user was trained in XXXXX in spring 1903.

2nd occurence:

Name: 4.01.072

Content:

 When pressing the About button, the following information should be shown:
    iAuthorize DLL version: [Major].[Minor].[Bug].[0] 
    Workflow Script version:    [Major].[Minor].[Bug]
    Std. Template version:  [XXXXX VX or XXXXXX VX]

The HP XXXX XXXXX version should be the name of the item called "XXXXX Version" on the XXXXX project list.

Please advise.

Regards, Srihari

Sounds like an obvious `RegEx` job to me. Split the text by carriage return and search each substring. This will give you the 'location' or context of each entry found. See https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Miqi180, May 28 '18 at 12:29

score 0 · Accepted Answer · answered May 28 '18 at 12:51

You could use a regular expression to match the "headings" and then derive the text content from the positions you get.

Assuming your input text is stored in variable s, then:

Dim re As Object
Dim dict As Object
Dim offset As Long
Dim hit As Object
Dim key As Variant
Dim results As Object

Set dict = CreateObject("Scripting.Dictionary")
Set re = CreateObject("VBScript.RegExp")
re.Global = True
re.Pattern = "\[\d\.\d\d\.\d\d\d\]|$"
offset = -1
Set results = re.Execute(s)
For Each hit In results
    If offset >= 0 Then dict.Add key, Trim(Mid(s, offset, hit.FirstIndex - offset))
    offset = hit.FirstIndex + 1 + Len(hit.Value)
    key = hit.Value
Next

' Output - just to show what you have collected:
For Each key In dict.Keys
    Debug.Print key  '      [d.ddd.dd]
    Debug.Print dict(key) ' corresponding text
Next

Thanks trincot for your valuable input, sometimes I am getting error message "key already exists in the collection" and program execution is halting.in such cases what can we do to continue with program execution avoiding termination? — Srihari, May 28 '18 at 17:09
That depends on what you want to happen when you have a duplicate section header in your input... Should the corresponding text be the same (and ignored), or should the two texts be concatenated together under one heading, or ... — trincot, May 28 '18 at 18:20

How to achieve below scenario using VBScript or VBA?

1 Answers1