VBA PROGRAM MODULES

On this page you will find some pieces of VBA scripts for Excel spreadsheet manipulation, which I wrote to speed up my work. VBA programming is very similar to VBScript, Visual Basic, and BASIC in general. These subs and functions mostly do string manipulation. Just copy and paste what you need into your own projects. 


' VBA Scripts by Zsolt N. Perry (zsnp@juno.com)
' Written in January 2023, Pensacola, Fla.
' FREEWARE. Feel free to use this code in your projects.
' No need to ask for permission. Just copy and paste.
'

''''''''''''''''''''''''''''''''''''''''''''''''''
'                                File | v2023.1.17
' This sub saves the content of Excel sheet1 into
' a CSV file. This function takes no arguments
' and does not modify the spreadsheet in any way.
'
' Usage: SaveSheet
'
Sub SaveSheet()

  OUTPUTDATA = ""
  MYLINE = ""
  LINELEN = 0

  For Y = 1 To 33
    MYLINE = ""
    LINELEN = 0
    For Letter = 0 To 25
      V = Range(Chr$(Letter + 65) & Y).Value
      LINELEN = LINELEN + Len(V)
      MYLINE = MYLINE & V & ","
    Next Letter
    If LINELEN > 0 Then
      RTRIMCHAR MYLINE, ","
      OUTPUTDATA = OUTPUTDATA & MYLINE & Chr$(13) & Chr$(10)
    End If
  Next Y
 
  CreateFile "D:\DESKTOP\TEMPFILE.CSV", OUTPUTDATA
  
  OUTPUTDATA = ""
  MYLINE = ""
  LINELEN = 0
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''
'                                File | v2023.1.17
' This sub creates an ASCII text file and
' writes the string CONTENT in it. If the file
' already exists, it will be deleted, and a new
' one will be created in its place.
'
' Usage: CreateFile STRING, STRING
'
Sub CreateFile(FILENAME, CONTENT)
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set File = FSO.CreateTextFile(FILENAME, True)
    File.Write (CONTENT)
    File.Close
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''
'                              String | v2023.1.17
' This sub trims characters from the right side
' of string S. The characters that are going to be
' removed from the right side are to be listed in
' string TC. String S is the input string.
' This sub will overwrite string S!!!
' Example: RTRIMCHAR LL, ", "  - This will trim both spaces and
'                               commas from the end of string LL
'
' Usage: RTRIMCHAR STRING, STRING
'
Sub RTRIMCHAR(S, TC)
    EndofLine = Len(S)
    For I = EndofLine To 1 Step -1
        Letter = Mid(S, I, 1)
        If InStr(TC, Letter) = 0 Then Exit For
        EndofLine = I
    Next I
    ' Truncate String:
    S = Mid(S, 1, EndofLine - 1)
End Sub
'''''''''''''''''''''''''''''''''''''''''''''''''''''
'                                 String | v2023.1.17
' This function removes illegal characters from string S.
' The only characters allowed in string S are listed in string A.
'
' Usage: STRING = TR(STRING, STRING)
'
Function TR(S, A)
    Z = ""  ' Output string
    For I = 1 To Len(S)
        C = Mid(S, I, 1)
        If InStr(A, C) Then Z = Z & C
    Next I
    TR = Z
    Z = ""
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''
'                            Graphics | v2023.1.17
' This function draws a bunch of random lines over
' an Excel spreadsheet.
'
' Usage: DrawRandomLines
'
'
Sub DrawRandomLines()
    For i = 0 To 100
        ActiveSheet.Shapes.AddLine(Rnd * 100, Rnd * 100, Rnd * 400, Rnd * 400).Select
        Selection.ShapeRange.Line.Weight = Rnd * 2
        Selection.ShapeRange.Line.Visible = msoTrue
        Selection.ShapeRange.Line.Style = msoLineSingle
        Selection.ShapeRange.Line.ForeColor.SchemeColor = Rnd * 22 + 3
        Selection.ShapeRange.Line.Visible = msoTrue
    Next i
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''
Last Update: Jan 17, 2023