C# Snippets

Col_Align

public void Col_Align(string sColFirst, string sColLast = "", string sDirection = "LEFT")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Microsoft.Office.Interop.Excel.Range objrange;

        if ((sColLast.Length == 0))
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns(sColFirst + ":" + sColLast);

        if (sDirection != "LEFT" & sDirection != "RIGHT" & sDirection != "CENTER")
            clszMessagesGeneral.Message("Incorrect direction '" + sDirection + "' " + gsCRLF + "sDirection must be either 'LEFT', 'RIGHT' or 'CENTER'.");
        if (sDirection == "LEFT")
            objrange.HorizontalAlignment = Excel.Constants.xlLeft;
        if (sDirection == "RIGHT")
            objrange.HorizontalAlignment = Excel.Constants.xlRight;
        if (sDirection == "CENTER")
            objrange.HorizontalAlignment = Excel.Constants.xlCenter;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            if (sColLast.Length > 0)
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle("WidthDefine", msCLASSNAME, "to align the" + serrortext + " in the '" + sDirection + "' direction.", mobjCOMException, mobjException);
        }
    }
}

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 void Col_AutoFit(string sColFirst, string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Microsoft.Office.Interop.Excel.Range objrange;

        if ((sColLast.Length == 0))
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns(sColFirst + ":" + sColLast);

        objrange.AutoFit();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            if (sColLast.Length > 0)
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle("AutoFit", msCLASSNAME, "autofit the" + serrortext + ".", mobjCOMException, mobjException);
        }
    }
}

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 void Col_Copy(string sColFirst, string sColLast = "", string sFromWshName = "", string sFromWbkName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Workbook objworkbookbefore;
        Excel.Workbook objworkbook;

        Excel.Worksheet objworksheetbefore;
        Excel.Worksheet objworksheet;

        Microsoft.Office.Interop.Excel.Range objrange;

        if (sFromWbkName.Length > 0)
        {
            objworkbookbefore = gApplicationExcel.ActiveWorkbook;

            objworkbook = gApplicationExcel.Workbooks(sFromWbkName);
            (Excel._Workbook)objworkbook.Activate();
        }

        if (sFromWshName.Length > 0)
        {
            objworksheetbefore = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

            objworksheet = (Excel.Worksheet)gApplicationExcel.Worksheets(sFromWshName);
            objworksheet.Select();
        }

        if ((sColLast.Length == 0))
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns(sColFirst + ":" + sColLast);

        objrange.Copy();

        if (sFromWbkName.Length > 0)
            (Excel._Workbook)objworkbookbefore.Activate();
        if (sFromWshName.Length > 0)
            objworksheetbefore.Select();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            if (sColLast.Length > 0)
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";
            if (sFromWshName.Length == 0)
                serrortext = serrortext + "from the active worksheet";
            if (sFromWshName.Length > 0)
                serrortext = serrortext + "from worksheet '" + sFromWshName + "'";
            if (sFromWbkName.Length > 0)
                serrortext = serrortext + "in the workbook '" + sFromWbkName + "'";

            clsError.Handle("Copy", msCLASSNAME, "copy the" + serrortext + ".", mobjCOMException, mobjException);
        }
    }
}

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 static string Col_LastUsedCol(string sWshName = "")
{
    Excel.Worksheet objWorksheet;
    Excel.Range objRange;

    try
    {
        if (clsError.ErrorFlag() == true)
            return;

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

        LastUsedCol = clsCol.Letter(objRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("LastUsedCol", msCLASSNAME, "", mobjCOMException, mobjException);
    }
}

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 static string Col_Letter(int iColNo)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        int inumber1;

        switch (iColNo)
        {
            case 0:
                {
                    Letter = Strings.Chr(90);
                    break;
                }

            case object _ when iColNo <= 26:
                {
                    Letter = Strings.Chr(iColNo + 64);
                    break;
                }

            default:
                {
                    inumber1 = System.Convert.ToInt32(Math.Floor((64 + ((iColNo - 1) / (double)26))));
                    Letter = Strings.Chr(inumber1) + Strings.Chr(((iColNo - 1) % 26) + 65);
                    break;
                }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
            clsError.Handle("Letter", "clsCol", "return the corresponding letter for the column number " + "'" + iColNo + "'.", mobjCOMException, mobjException);
    }
}

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 static int Col_Number(string sColChar, string sWshName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        int istartnumber;

        Microsoft.Office.Interop.Excel.Worksheet objworksheet;
        Microsoft.Office.Interop.Excel.Range objrange;

        if (Strings.Len(sColChar) == 1)
        {
            if (sWshName != "")
            {
                objworksheet = (Excel.Worksheet)gApplicationExcel.Worksheets(sWshName);

                Number = objworksheet.Range(sColChar + "1").Column;
            }
            if (sWshName == "")
            {
                objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

                Number = objworksheet.Range(sColChar + "1").Column;
            }
        }
        else
        {
            objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

            istartnumber = objworksheet.Range((Strings.Left(sColChar, 1)) + "1").Column;
            Number = ((istartnumber)) * 26 * (Strings.Len(sColChar) - 1) + clsCol.Number(Strings.Right(sColChar, Strings.Len(sColChar) - 1));
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
            clsError.Handle("Number", "clsCol", "return the corresponding number for the column letter " + "'" + sColChar + "'.", mobjCOMException, mobjException);
    }
}

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 void Col_SelectCol(string sColFirst, string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Microsoft.Office.Interop.Excel.Range objrange;

        if ((sColLast.Length == 0))
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns(sColFirst + ":" + sColLast);

        objrange.Select();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            if (sColLast.Length > 0)
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle("SelectCol", msCLASSNAME, "select the" + serrortext + "'.", mobjCOMException, mobjException);
        }
    }
}

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 void Col_WidthDefine(float sngColWidth, string sColFirst, string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Microsoft.Office.Interop.Excel.Range objrange;

        if ((sColLast.Length == 0))
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns(sColFirst + ":" + sColLast);

        objrange.ColumnWidth = sngColWidth;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
        {
            string serrortext;

            if (sColLast.Length == 0)
                serrortext = " column '" + sColFirst + "'";
            if (sColLast.Length > 0)
                serrortext = " columns '" + sColFirst + ":" + sColLast + "'";

            clsError.Handle("WidthDefine", msCLASSNAME, "define the width of " + serrortext + " to '" + sngColWidth + "' points.", mobjCOMException, mobjException);
        }
    }
}

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 static float Col_WidthToPoints(string sColFirst, string sColLast = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Microsoft.Office.Interop.Excel.Range objrange;

        if (Strings.Len(sColLast) == 0)
            sColLast = sColFirst;

        objrange = (Excel.Range)gApplicationExcel.Columns(sColFirst + ":" + sColLast);

        WidthToPoints = System.Convert.ToSingle(objrange.Width);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
        {
            string serrortext;

            if (Strings.Len(sColFirst) == 0)
                serrortext = " column \"" + sColFirst + "\".";
            if (Strings.Len(sColLast) > 0)
                serrortext = " columns \"" + sColFirst + ":" + sColLast + "\".";

            clsError.Handle("WidthToPoints", "clsCol", "return the corresponding column width for" + serrortext, mobjCOMException, mobjException);
        }
    }
}

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 static float DeleteCols(params string[] asColumns)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objColumns;
        int icount;
        string scolumnfirst;
        string scolumnlast;

        for (icount = 1; icount <= System.Convert.ToInt32(asColumns.Length / (double)2); icount++)
        {
            scolumnfirst = asColumns[2 * (icount - 1)];
            scolumnlast = asColumns[2 * (icount - 1) + 1];

            objColumns = (Excel.Range)gApplicationExcel.Columns(scolumnfirst + ":" + scolumnlast);

            objColumns.Delete(Shift: Excel.XlDeleteShiftDirection.xlShiftToLeft);
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
            clsError.Handle("DeleteCols", "clsCol", "delete the columns.", mobjCOMException, mobjException);
    }
}

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 static float InsertCols(params string[] asColumns)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objColumns;
        int icount;
        string scolumnfirst;
        string scolumnlast;

        for (icount = 1; icount <= System.Convert.ToInt32(asColumns.Length / (double)2); icount++)
        {
            scolumnfirst = asColumns[2 * (icount - 1)];
            scolumnlast = asColumns[2 * (icount - 1) + 1];

            objColumns = (Excel.Range)gApplicationExcel.Columns(scolumnfirst + ":" + scolumnlast);

            objColumns.Insert(Shift: Excel.XlInsertShiftDirection.xlShiftToRight);
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
            clsError.Handle("InsertCols", "clsCol", "insert the columns.", mobjCOMException, mobjException);
    }
}

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 static float Row_HeightToPoints(int iRowFirst, int iRowLast = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objrange;

        if (iRowLast == 0)
            iRowLast = iRowFirst;

        objrange = (Excel.Range)gApplicationExcel.Range("A" + iRowFirst + ":" + "A" + iRowLast);

        HeightToPoints = System.Convert.ToSingle(objrange.Height);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(mobjCOMException) == false | IsNothing(mobjException) == false)))
        {
            string serrortext;

            if (Strings.Len(iRowLast) == 0)
                serrortext = " row \"" + iRowFirst + "\"";
            if (Strings.Len(iRowLast) > 0)
                serrortext = " rows \"" + iRowFirst + ":" + iRowLast + "\"";

            clsError.Handle("HeightToPoints", "clsRow", "return the corresponding row height for" + serrortext, mobjCOMException, mobjException);
        }
    }
}

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 static Int32 Row_LastUsedRow(string sWshName = "")
{
    Excel.Worksheet objWorksheet;
    Excel.Range objRange;

    try
    {
        if (clsError.ErrorFlag() == true)
            return;

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

        LastUsedRow = objRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("LastUsedRow", msCLASSNAME, "", mobjCOMException, mobjException);
    }
}

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 static Int32 Row_LastUsedRowQuick(string sColChar, string sWshName = "")
{
    Excel.Worksheet objWorksheet;
    Excel.Range objRange;

    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
        objRange = (Excel.Range)objWorksheet.Range(sColChar + gi64TOTALROWS);

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

        LastUsedRowQuick = objRange.Row;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("LastUsedRowQuick", msCLASSNAME, "", mobjCOMException, mobjException);
    }
}

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 static float Rows_InsertRows(params long[] alRows)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objRows;
        int icount;
        long lrowfirst;
        long lrowlast;

        for (icount = 1; icount <= System.Convert.ToInt32(alRows.Length / (double)2); icount++)
        {
            lrowfirst = alRows[2 * (icount - 1)];
            lrowlast = alRows[2 * (icount - 1) + 1];

            objRows = (Excel.Range)gApplicationExcel.Rows(lrowfirst + ":" + lrowlast);

            objRows.Insert(Shift: Excel.XlInsertShiftDirection.xlShiftDown);
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("InsertRows", "clsRows", "insert the rows.", mobjCOMException, mobjException);
    }
}

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 static void Rows_ShadeAlternate(long lFirstColourIndex, long lSecondColourIndex, params Int32[] iArrayRows)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objRows;
        int icount;
        long lrowwfirst;
        long lrowwlast;
        Int32 inumberofrows;
        long lrownumber;

        for (icount = 1; icount <= System.Convert.ToInt32((iArrayRows.Length / (double)2)); icount++)
        {
            lrowwfirst = iArrayRows[2 * (icount - 1)];
            inumberofrows = iArrayRows[2 * (icount - 1) + 1];
            lrowwlast = lrowwfirst + inumberofrows - 1;
            lrownumber = lrowwfirst;
            while (!lrownumber > lrowwlast)
            {
                objRows = (Excel.Range)gApplicationExcel.Rows(lrownumber + ":" + lrownumber);
                objRows.Interior.ColorIndex = lFirstColourIndex;

                lrownumber = lrownumber + 1;
                if (lrownumber <= lrowwlast)
                {
                    objRows = (Excel.Range)gApplicationExcel.Rows(lrownumber + ":" + lrownumber);
                    objRows.Interior.ColorIndex = lSecondColourIndex;

                    lrownumber = lrownumber + 1;
                }
            }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("ShadeAlternate", "clsRows", "shade the rows ?? " + "alternatively with the colours " + "'" + lFirstColourIndex + "' and '" + lSecondColourIndex + "'", mobjCOMException, mobjException);
    }
}

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

---

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