C# Snippets

Col_Align

Public Sub Col_Align(ByVal sColFirst As String, _
        Optional ByVal sColLast As String = "", _
        Optional ByVal sDirection As String = "LEFT")

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If (sColLast.Length = 0) Then sColLast = sColFirst

      objrange = CType(gApplicationExcel.Columns(sColFirst & ":" & sColLast), _
                       Excel.Range)

      If sDirection <> "LEFT" And sDirection <> "RIGHT" And sDirection <> "CENTER" Then
         Call clszMessagesGeneral.Message( _
              "Incorrect direction '" & sDirection & "' " & gsCRLF & _
              "sDirection must be either 'LEFT', 'RIGHT' or 'CENTER'.")
      End If
      If sDirection = "LEFT" Then objrange.HorizontalAlignment = Excel.Constants.xlLeft
      If sDirection = "RIGHT" Then objrange.HorizontalAlignment = Excel.Constants.xlRight
      If sDirection = "CENTER" Then objrange.HorizontalAlignment = Excel.Constants.xlCenter

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Dim serrortext As String

         If sColLast.Length = 0 Then serrortext = " column '" & sColFirst & "'"
         If sColLast.Length > 0 Then serrortext = " columns '" & sColFirst & ":" & sColLast & "'"

         Call clsError.Handle("WidthDefine", msCLASSNAME, _
              "to align the" & serrortext & " in the '" & sDirection & "' direction.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub
'****************************************************************************************

Col_AutoFit

Public Sub Col_AutoFit(ByVal sColFirst As String, _
          Optional ByVal sColLast As String = "")

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If (sColLast.Length = 0) Then sColLast = sColFirst

      objrange = CType(gApplicationExcel.Columns(sColFirst & ":" & sColLast), _
                       Excel.Range)

      objrange.AutoFit()

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Dim serrortext As String

         If sColLast.Length = 0 Then serrortext = " column '" & sColFirst & "'"
         If sColLast.Length > 0 Then serrortext = " columns '" & sColFirst & ":" & sColLast & "'"

         Call clsError.Handle("AutoFit", msCLASSNAME, _
              "autofit the" & serrortext & ".", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub
'****************************************************************************************

Col_Copy

Public Sub Col_Copy(ByVal sColFirst As String, _
       Optional ByVal sColLast As String = "", _
       Optional ByVal sFromWshName As String = "", _
       Optional ByVal sFromWbkName As String = "")

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objworkbookbefore As Excel.Workbook
      Dim objworkbook As Excel.Workbook

      Dim objworksheetbefore As Excel.Worksheet
      Dim objworksheet As Excel.Worksheet

      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If sFromWbkName.Length > 0 Then
         objworkbookbefore = gApplicationExcel.ActiveWorkbook

         objworkbook = gApplicationExcel.Workbooks(sFromWbkName)
         CType(objworkbook, Excel._Workbook).Activate()
      End If

      If sFromWshName.Length > 0 Then
         objworksheetbefore = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)

         objworksheet = CType(gApplicationExcel.Worksheets(sFromWshName), Excel.Worksheet)
         objworksheet.Select()
      End If

      If (sColLast.Length = 0) Then sColLast = sColFirst

      objrange = CType(gApplicationExcel.Columns(sColFirst & ":" & sColLast), _
                       Excel.Range)

      objrange.Copy()

      If sFromWbkName.Length > 0 Then CType(objworkbookbefore, Excel._Workbook).Activate()
      If sFromWshName.Length > 0 Then objworksheetbefore.Select()

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Dim serrortext As String

         If sColLast.Length = 0 Then serrortext = " column '" & sColFirst & "'"
         If sColLast.Length > 0 Then serrortext = " columns '" & sColFirst & ":" & sColLast & "'"
         If sFromWshName.Length = 0 Then serrortext = serrortext & "from the active worksheet"
         If sFromWshName.Length > 0 Then serrortext = serrortext & "from worksheet '" & sFromWshName & "'"
         If sFromWbkName.Length > 0 Then serrortext = serrortext & "in the workbook '" & sFromWbkName & "'"

         Call clsError.Handle("Copy", msCLASSNAME, _
              "copy the" & serrortext & ".", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub
'****************************************************************************************

Col_LastUsed

Public Shared Function Col_LastUsedCol(Optional ByVal sWshName As String = "") _
                                   As String

   Dim objWorksheet As Excel.Worksheet
   Dim objRange As Excel.Range

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      objWorksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
      objRange = CType(objWorksheet.Range("A1"), Excel.Range)

      LastUsedCol = clsCol.Letter(objRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column)

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("LastUsedCol", msCLASSNAME, _
              "", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Col_Letter

Converting a column number to its equivalent column letter.

Public Shared Function Col_Letter(ByVal iColNo As Integer) _
                              As String

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim inumber1 As Integer

      Select Case iColNo
         Case 0 : Letter = Chr(90)
         Case Is <= 26 : Letter = Chr(iColNo + 64)
         Case Else
            inumber1 = CInt(Math.Floor((64 + ((iColNo - 1) / 26))))
            Letter = Chr(inumber1) & Chr(((iColNo - 1) Mod 26) + 65)

      End Select

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("Letter", "clsCol", _
              "return the corresponding letter for the column number " & _
              "'" & iColNo & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function

Col_Number

Converting a column letter to its equivalent column number.

Public Shared Function Col_Number(ByVal sColChar As String, _
                     Optional ByVal sWshName As String = "") As Integer

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim istartnumber As Integer

      Dim objworksheet As Microsoft.Office.Interop.Excel.Worksheet
      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If Len(sColChar) = 1 Then
         If sWshName <> "" Then
            objworksheet = CType(gApplicationExcel.Worksheets(sWshName), Excel.Worksheet)

            Number = objworksheet.Range(sColChar & "1").Column
         End If
         If sWshName = "" Then
            objworksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)

            Number = objworksheet.Range(sColChar & "1").Column

         End If
      Else
         objworksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)

         istartnumber = objworksheet.Range((Left(sColChar, 1)) & "1").Column
         Number = ((istartnumber)) * 26 * (Len(sColChar) - 1) + _
                                         clsCol.Number(Right(sColChar, Len(sColChar) - 1))
      End If

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("Number", "clsCol", _
              "return the corresponding number for the column letter " & _
              "'" & sColChar & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Col_SelectCol

Public Sub Col_SelectCol(ByVal sColFirst As String, _
            Optional ByVal sColLast As String = "")

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If (sColLast.Length = 0) Then sColLast = sColFirst

      objrange = CType(gApplicationExcel.Columns(sColFirst & ":" & sColLast), _
                       Excel.Range)

      objrange.Select()

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Dim serrortext As String

         If sColLast.Length = 0 Then serrortext = " column '" & sColFirst & "'"
         If sColLast.Length > 0 Then serrortext = " columns '" & sColFirst & ":" & sColLast & "'"

         Call clsError.Handle("SelectCol", msCLASSNAME, _
              "select the" & serrortext & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub
'****************************************************************************************

Col_WidthDefine

Public Sub Col_WidthDefine(ByVal sngColWidth As Single, _
                       ByVal sColFirst As String, _
              Optional ByVal sColLast As String = "")

   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If (sColLast.Length = 0) Then sColLast = sColFirst

      objrange = CType(gApplicationExcel.Columns(sColFirst & ":" & sColLast), _
                       Excel.Range)

      objrange.ColumnWidth = sngColWidth

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Dim serrortext As String

         If sColLast.Length = 0 Then serrortext = " column '" & sColFirst & "'"
         If sColLast.Length > 0 Then serrortext = " columns '" & sColFirst & ":" & sColLast & "'"

         Call clsError.Handle("WidthDefine", msCLASSNAME, _
              "define the width of " & serrortext & " to '" & sngColWidth & "' points.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub
'****************************************************************************************

Col_WidthToPoints

Public Shared Function Col_WidthToPoints(ByVal sColFirst As String, _
                            Optional ByVal sColLast As String = "") As Single

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim objrange As Microsoft.Office.Interop.Excel.Range

      If Len(sColLast) = 0 Then sColLast = sColFirst

      objrange = CType(gApplicationExcel.Columns(sColFirst & ":" & sColLast), _
                       Excel.Range)

      WidthToPoints = CType(objrange.Width, Single)

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Dim serrortext As String

         If Len(sColFirst) = 0 Then serrortext = " column """ & sColFirst & """."
         If Len(sColLast) > 0 Then _
            serrortext = " columns """ & sColFirst & ":" & sColLast & """."

         Call clsError.Handle("WidthToPoints", "clsCol", _
              "return the corresponding column width for" & serrortext, _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Cols_DeleteCols

Public Shared Function DeleteCols(ByVal ParamArray asColumns() As String) As Single

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim objColumns As Excel.Range
      Dim icount As Integer
      Dim scolumnfirst As String
      Dim scolumnlast As String

      For icount = 1 To CInt(asColumns.Length / 2)
         scolumnfirst = asColumns(2 * (icount - 1))
         scolumnlast = asColumns(2 * (icount - 1) + 1)

         objColumns = CType(gApplicationExcel.Columns(scolumnfirst & ":" & scolumnlast), Excel.Range)

         objColumns.Delete(Shift:=Excel.XlDeleteShiftDirection.xlShiftToLeft)

      Next icount

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("DeleteCols", "clsCol", _
              "delete the columns.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Cols_InsertCols

Public Shared Function InsertCols(ByVal ParamArray asColumns() As String) As Single

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim objColumns As Excel.Range
      Dim icount As Integer
      Dim scolumnfirst As String
      Dim scolumnlast As String

      For icount = 1 To CInt(asColumns.Length / 2)
         scolumnfirst = asColumns(2 * (icount - 1))
         scolumnlast = asColumns(2 * (icount - 1) + 1)

         objColumns = CType(gApplicationExcel.Columns(scolumnfirst & ":" & scolumnlast), Excel.Range)

         objColumns.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftToRight)

      Next icount

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Call clsError.Handle("InsertCols", "clsCol", _
              "insert the columns.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Row_HeightToPoints

Public Shared Function Row_HeightToPoints(ByVal iRowFirst As Integer, _
                             Optional ByVal iRowLast As Integer = 0) As Single

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim objrange As Excel.Range

      If iRowLast = 0 Then iRowLast = iRowFirst

      objrange = CType(gApplicationExcel.Range("A" & iRowFirst & ":" & "A" & iRowLast), _
                        Excel.Range)

      HeightToPoints = CType(objrange.Height, Single)

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(mobjCOMException) = False Or IsNothing(mobjException) = False)) Then

         Dim serrortext As String

         If Len(iRowLast) = 0 Then serrortext = " row """ & iRowFirst & """"
         If Len(iRowLast) > 0 Then _
            serrortext = " rows """ & iRowFirst & ":" & iRowLast & """"

         Call clsError.Handle("HeightToPoints", "clsRow", _
              "return the corresponding row height for" & serrortext, _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Row_LastUsed

Public Shared Function Row_LastUsedRow(Optional ByVal sWshName As String = "") _
                                   As Int32

   Dim objWorksheet As Excel.Worksheet
   Dim objRange As Excel.Range

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      objWorksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
      objRange = CType(objWorksheet.Range("A1"), Excel.Range)

      LastUsedRow = objRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("LastUsedRow", msCLASSNAME, _
              "", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Row_LastUsedQuick

Public Shared Function Row_LastUsedRowQuick(ByVal sColChar As String, _
                               Optional ByVal sWshName As String = "") _
                                        As Int32

   Dim objWorksheet As Excel.Worksheet
   Dim objRange As Excel.Range

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      objWorksheet = CType(gApplicationExcel.ActiveSheet, Excel.Worksheet)
      objRange = CType(objWorksheet.Range(sColChar & gi64TOTALROWS), Excel.Range)

      objRange = objRange.End(Excel.XlDirection.xlUp)

      LastUsedRowQuick = objRange.Row

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("LastUsedRowQuick", msCLASSNAME, _
              "", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Rows_InsertRows

Public Shared Function Rows_InsertRows(ByVal ParamArray alRows() As Long) _
                                  As Single

   Try
      If clsError.ErrorFlag() = True Then Exit Function

      Dim objRows As Excel.Range
      Dim icount As Integer
      Dim lrowfirst As Long
      Dim lrowlast As Long

      For icount = 1 To CInt(alRows.Length / 2)
         lrowfirst = alRows(2 * (icount - 1))
         lrowlast = alRows(2 * (icount - 1) + 1)

         objRows = CType(gApplicationExcel.Rows(lrowfirst & ":" & lrowlast), Excel.Range)

         objRows.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftDown)

      Next icount

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("InsertRows", "clsRows", _
              "insert the rows.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function
'****************************************************************************************

Rows_ShadeAlternate

Public Shared Sub Rows_ShadeAlternate(ByVal lFirstColourIndex As Long, _
                                 ByVal lSecondColourIndex As Long, _
                                 ByVal ParamArray iArrayRows() As Int32)


   Try
      If clsError.ErrorFlag() = True Then Exit Sub

      Dim objRows As Excel.Range
      Dim icount As Integer
      Dim lrowwfirst As Long
      Dim lrowwlast As Long
      Dim inumberofrows As Int32
      Dim lrownumber As Long

      For icount = 1 To CType((iArrayRows.Length / 2), Integer)
         lrowwfirst = iArrayRows(2 * (icount - 1))
         inumberofrows = iArrayRows(2 * (icount - 1) + 1)
         lrowwlast = lrowwfirst + inumberofrows - 1
         lrownumber = lrowwfirst
         Do Until lrownumber > lrowwlast

            objRows = CType(gApplicationExcel.Rows(lrownumber & ":" & lrownumber), Excel.Range)
            objRows.Interior.ColorIndex = lFirstColourIndex

            lrownumber = lrownumber + 1
            If lrownumber <= lrowwlast Then

               objRows = CType(gApplicationExcel.Rows(lrownumber & ":" & lrownumber), Excel.Range)
               objRows.Interior.ColorIndex = lSecondColourIndex

               lrownumber = lrownumber + 1
            End If
         Loop
      Next icount

   Catch objCOMException As System.Runtime.InteropServices.COMException
      mobjCOMException = objCOMException
   Catch objException As Exception
      mobjException = objException

   Finally
      If gbDEBUG_EXCEL = True Or _
         ((Not mobjCOMException Is Nothing) Or (Not mobjException Is Nothing)) Then

         Call clsError.Handle("ShadeAlternate", "clsRows", _
              "shade the rows ?? " & _
              "alternatively with the colours " & _
              "'" & lFirstColourIndex & "' and '" & lSecondColourIndex & "'", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub
'****************************************************************************************

---

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top