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

Statistics

  • Entries (1)
  • Comments (0)

Replace Words with Abbreviations to make Descriptions Shorter 

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

Overview

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

LongShortKeywords
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)
    trs.OpenRecordset
    s = LongDescrip
    Do While Not trs.EOF
        If InStr(1, s, trs!LongDescripKeyword) Then
            s = Replace(s, trs!LongDescripKeyword, trs!ShortDescripKeyword)
        End If
        trs.MoveNext
    Loop
ShortDescrip = s
End Function