Code Snippets That May be Useful

Here are some VBA code snippets that may be useful because sometimes a little code is all you need

VBA Code


  • Entries (1)
  • Comments (0)

Replace Words with Abbreviations to make Descriptions Shorter 

Thursday, May 10, 2012 10:20:00 AM Categories: Access Collections Excel


Many times you may need the short form for a description in order to fit the description into a PLC or DCS system.  Example: MTR for Motor

Here is a function that works with a table of abbreviations to change strings inside of strings to shorten the description.

The table should look like this

LongDescripKeyword ShortDescripKeyword SortOrder
Motor MTR 1
Protective Relay RLY 2
Temperature TEMP 3
Lockout Relay LOR 4







Function ShortDescrip(LongDescrip As String) As String
    Dim trs As DAO.Recordset
    Dim db As DAO.Database
    Dim SQL As String, vartemp As Variant
    Dim cLong As New Collection
    Dim cShort As New Collection
    Dim s As String, l As String
    Set db = CurrentDb
    SQL = "SELECT * FROM LongShortKeywords ORDER BY SortOrder;"
    Set trs = db.OpenRecordset(SQL)
    s = LongDescrip
    Do While Not trs.EOF
        If InStr(1, s, trs!LongDescripKeyword) Then
            s = Replace(s, trs!LongDescripKeyword, trs!ShortDescripKeyword)
        End If
ShortDescrip = s
End Function