Abbreviations Validation

mrsandes · #1 04-07-2020, 06:47 AM

Hi guys,

I have been reading a lot through the topics here but couldn't find the help for what I need.

I'm not an expert in macros but I have been working on a project that requires me to do some VBA macros to support others.

I'm currently working on a macro which aims on finding pre-defined abbreviations within a document and having them listed in a certain table in the document with their definitions.

As I have over 300 abbreviations pre-defined, I thought of looping the "find" with something around the following code:

Code:

Sub Abbreviations()

Dim x As Integer
Dim result As String

abb1 = "A/C"
abb2 = "AAR"
def1 = "Aircraft"
def2 = "Aircraft Architecture Review"

For x = 1 To 2

    result = "abb" & x

Debug.Print ("Result: " + result)

    Next x
    
End Sub

However, instead of showing me the result as previously defined, it showed me "abb1" and "abb2" for x = 1 and 2.

Would you guys know how to make it show what was pre-defined, instead?

Thanks in advance.

gmaxey · #2 04-07-2020, 12:05 PM

To be perfectly honest, what you have typed and described makes little sense. You might try something like:

Code:

Sub Abbreviations()
Dim lngIndex As Long
Dim arrTerms(1, 1)
  arrTerms(0, 0) = "A/C"
  arrTerms(1, 0) = "AAR"
  arrTerms(0, 1) = "Aircraft"
  arrTerms(1, 1) = "Aircraft Architecture Review"
  For lngIndex = 0 To UBound(arrTerms, 1)
    Debug.Print arrTerms(lngIndex, 0) & " - "; arrTerms(lngIndex, 1)
  Next lngIndex
lbl_Exit:
  Exit Sub
End Sub

macropod · #3 04-07-2020, 03:06 PM

See: https://www.msofficeforums.com/word-...generator.html

mrsandes · #4 04-08-2020, 12:19 AM

Quote:

Originally Posted by gmaxey

To be perfectly honest, what you have typed and described makes little sense. You might try something like:

Code:

Sub Abbreviations()
Dim lngIndex As Long
Dim arrTerms(1, 1)
  arrTerms(0, 0) = "A/C"
  arrTerms(1, 0) = "AAR"
  arrTerms(0, 1) = "Aircraft"
  arrTerms(1, 1) = "Aircraft Architecture Review"
  For lngIndex = 0 To UBound(arrTerms, 1)
    Debug.Print arrTerms(lngIndex, 0) & " - "; arrTerms(lngIndex, 1)
  Next lngIndex
lbl_Exit:
  Exit Sub
End Sub

Thank you for your reply. Even tho I haven't expressed myself clearly, you managed to understand and delivered a code that works for my needs!

I'll just adapt now to search for these results.

Best,

Daniel

mrsandes · #5 04-08-2020, 12:20 AM

Quote:

Originally Posted by macropod

See: https://www.msofficeforums.com/word-...generator.html

Thanks for your reply.

Best,

Daniel

mrsandes · #6 04-08-2020, 02:29 AM

Hi Paul,

Thanks for sharing this amazing macro, it is working.
However, I have tried modifying a few things to make it simpler without success... I wanted to have the generated table as only 2 columns, the abbreviations and the meaning. I would like to run a validation test on the abbreviations and meanings generated as well based on a pre-defined list that I have, to make sure only pre-approved abbreviations were used and with the pre-assigned meaning to them.

Would you see that as a possible thing with VBA?

mrsandes · #7 04-08-2020, 06:56 AM

Quote:

Originally Posted by mrsandes

Hi Paul,

Thanks for sharing this amazing macro, it is working.
However, I have tried modifying a few things to make it simpler without success... I wanted to have the generated table as only 2 columns, the abbreviations and the meaning. I would like to run a validation test on the abbreviations and meanings generated as well based on a pre-defined list that I have, to make sure only pre-approved abbreviations were used and with the pre-assigned meaning to them.

Would you see that as a possible thing with VBA?

I managed to get the validation part up and running using a third column to show the "result". The only thing I am struggling with now is taking out everything related to what the columns 3 to 5 did in the default macro and having the macro creating only 3 columns... Your help would be much appreciated.

Macro used for validation:

Code:

Sub Validate()
Application.ScreenUpdating = False

Dim Tbl As Table, r As Long, Abb_Fd As String, Def_Fd As String, x As Long
Dim accept As String, reject1 As String, reject2 As String
    
accept = "Approved"
reject1 = "Unapproved Abbreviation/Acronym"
reject2 = "Unapproved Definition to Abbreviation/Acronym"

Dim arrTerms(1, 1)

arrTerms(0, 0) = "A/C"
arrTerms(1, 0) = "AAR"
arrTerms(0, 1) = "Aircraft"
arrTerms(1, 1) = "Aircraft Architecture Review"
    
With ActiveDocument

  Set Tbl = .Tables(.Tables.Count)
  
  For r = 2 To Tbl.Rows.Count

      Abb_Fd = Split(Tbl.Cell(r, 1).Range.Text, vbCr)(0)
      Def_Fd = Split(Tbl.Cell(r, 2).Range.Text, vbCr)(0)
      
        For x = 0 To 364
        
            If Abb_Fd = arrTerms(x, 0) And Def_Fd = arrTerms(x, 1) Then
            Tbl.Cell(r, 3).Range = accept
            Exit For
            ElseIf Abb_Fd = arrTerms(x, 0) Then
            Tbl.Cell(r, 3).Range = reject2
            Exit For
            Else: Tbl.Cell(r, 3).Range = reject1
            End If
        Next x
    
  Next r
  
End With

Application.ScreenUpdating = True

End Sub

macropod · #8 04-08-2020, 07:18 AM

Change:
StrAcronyms = "Acronym" & vbTab & "Term" & vbTab & "Page" & vbTab & "Cross-Reference Count" & vbTab & "Cross-Reference Pages" & vbCr
to:
StrAcronyms = "Acronym" & vbTab & "Term" & vbTab & "Validation" & vbCr

Change:
StrAcronyms = StrAcronyms & Split(StrTmp, "|")(1) & vbTab & Split(StrTmp, "|")(0) & vbTab & .Information(wdActiveEndAdjustedPageNumber) & vbTab & vbTab & vbCr
to:
StrAcronyms = StrAcronyms & Split(StrTmp, "|")(1) & vbTab & Split(StrTmp, "|")(0) & vbCr

Change:
Set Tbl = .ConvertToTable(Separator:=vbTab, NumRows:=.Paragraphs.Count, NumColumns:=5)
to:
Set Tbl = .ConvertToTable(Separator:=vbTab, NumRows:=.Paragraphs.Count, NumColumns:=3)

Delete from:
For i = 2 To Tbl.Rows.Count
to:
Next

Delete the entire 'ParseNumSeq' function.

mrsandes · #9 04-09-2020, 04:04 AM

Thanks Paul.
I made these changes and added one more search possibility, as we may have a few characters between the capital letters in the acronmys, to do so I duplicated the code. Would you say there is a better way to do that?

I'm having the trouble of duplicates being listed in the table if they appear more than once in the text between parenthesis as well, would you know how to clear duplicates in word tables?

Thank you for your support so far and in advance if you take the time to support on these questions.

macropod · #10 04-09-2020, 05:35 AM

Quote:

Originally Posted by mrsandes

I made these changes and added one more search possibility, as we may have a few characters between the capital letters in the acronmys, to do so I duplicated the code. Would you say there is a better way to do that?

Probably, but since you haven't said what those characters are, it's impossible to say how the expression should be changed. That said, I certainly wouldn't use the expression you've used.

Quote:

Originally Posted by mrsandes

I'm having the trouble of duplicates being listed in the table if they appear more than once in the text between parenthesis as well, would you know how to clear duplicates in word tables?

The appropriate way is to fix the document - you shouldn't have the same acronym in parentheses in multiple locations.

mrsandes · #11 04-09-2020, 06:37 AM

Thanks for the quick reply, Paul.

Quote:

Originally Posted by macropod

Probably, but since you haven't said what those characters are, it's impossible to say how the expression should be changed. That said, I certainly wouldn't use the expression you've used.

The idea is to be able to use more than one find criteria. As we may have acronyms/abbreviations with 2 or more uppercase letters, as you had in your code, but there is also the possibility of having a lower case letter or a symbol in between uppercase letters.

I tried achieving that with the search:

Code:

.Text = "\(<([A-Z]{1,})?([A-Z]{1,})>\)"

but it wouldn't find uppercase letters only acronyms. From what I researched, using {0,} for the ? wouldn't work.

Quote:

Originally Posted by macropod

Try:

Code:

      .Sort ExcludeHeader:=True, FieldNumber:="Column 1", CaseSensitive:=False, _
        SortFieldType:=wdSortFieldAlphanumeric, SortOrder:=wdSortOrderAscending

That worked! Thanks a lot.

macropod · #12 04-09-2020, 06:49 PM

Quote:

Originally Posted by mrsandes

The idea is to be able to use more than one find criteria. As we may have acronyms/abbreviations with 2 or more uppercase letters, as you had in your code, but there is also the possibility of having a lower case letter or a symbol in between uppercase letters.

In that case, try:
.Text = "\([A-Z0-9][!\) ]@[A-Z0-9]\)"

mrsandes · #13 04-14-2020, 04:32 AM

Quote:

Originally Posted by macropod

In that case, try:
.Text = "\([A-Z0-9][!\) ]@[A-Z0-9]\)"

Thanks, Paul. The only problem with that is that it doesn't find the acronyms with 2 uppercase letters only.

Even though it is not the best approach, the double verification is working, so I will stick to that.

Thank you for your support!

macropod · #14 04-14-2020, 06:07 AM

You might try:
.Text = "\([A-Z0-9]*[A-Z0-9]\)"
but, depending on what else you have in parentheses, that may give some false matches.

mrsandes · #15 04-16-2020, 05:43 AM

Quote:

Originally Posted by macropod

You might try:
.Text = "\([A-Z0-9]*[A-Z0-9]\)"
but, depending on what else you have in parentheses, that may give some false matches.

Yes, it increased significantly the amount of false matches.

Paul, if I were to find also abbreviations that are not enclosed by parenthesis, what else would I have to delete on the code?

I just removed the "\(" and "\)" on the search mechanism, but it gives me an error "Subscript out of range" and highlights the line:

Code:

StrAcronyms = StrAcronyms & Split(StrTmp, "|")(1) & vbTab & Split(StrTmp, "|")(0) & vbCr

Another thing that I noticed is that the AcronymManager macro will not replace the text in the document for the acronym if the text is not capitalized in each word... What I mean is, if I have the acronym Part List (PL) found and listed in the acronyms table, it will update in the document every instance that "Part List" is written, replacing by "PL", but it will not replace the instances where "part list" is written. Would it be possible to change that setting?

Thanks for your help

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Creating a list of Acronyms / Abbreviations	daithy	Word	4	03-12-2020 01:52 PM
Data Validation in Microsoft Word Template	BradleyCase	Word VBA	1	07-16-2019 12:37 PM
Adding Professional Credential Abbreviations to Name	dyny723	Outlook	0	03-16-2018 07:14 AM
Automate Job application word doc to Excel (with data validation)	dylansmith	Office	1	02-11-2018 12:58 PM
validation email address in word text filed	sameerahmad_P	Word VBA	1	03-07-2014 02:59 PM