C# - Snippets

Address_ColumnLetter

Returns the column letter from a cell address containing a column and a row.

public string Address_ColumnLetter(
   string sCellAddress)
{
    string sreturn;

    // maybe dollars maybe not
    // maybe column is greater than 26

    // remove the initial $ and the following $1
    sreturn = Strings.Mid(sAddress, 2, Strings.Len(sAddress) - 3);

    Address_ColumnLetter = sreturn;
}

public void Test_Addresses()
{
    string srange;

    srange = Range("A1").Address;
    Debug.Print(Address_ColumnLetter(srange));
}

Cell_FormatNumber

public void Cell_FormatNumber(
   string sCellAddress, 
   string sNumberFormat)
{
    Excel.Worksheet objWorksheet;
    Excel.Range objRange;

    try
    {
        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        objRange = (Excel.Range)objWorksheet.Range(sCellAddress);

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

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

Cell_OffsetFormat

private static void Cell_OffsetFormat(
   Excel.Range objCell,
   int iRowOffset,
   int iColumnOffset,
   string sText,
   bool bBold,
   Excel.XlHAlign enHAlignment,
   string sNumberFormat)
{

    System.DateTime result;

    if (sNumberFormat.Length > 0)
    {
        objCell.get_Offset(iRowOffset, iColumnOffset).NumberFormat = sNumberFormat;
    }

    if (System.DateTime.TryParse(sText, out result) == false)
    {
        // Note that C# requires you to retrieve and set
        // the Value2 property of the Range, rather than 
        // the Value property, because the Value property 
        // is parameterized, making it unavailable to C# code:

        objCell.get_Offset(iRowOffset, iColumnOffset).Value2 = sText;
    }
    else
    {
        objCell.get_Offset(iRowOffset, iColumnOffset).Value2 = result;
    }

    objCell.get_Offset(iRowOffset, iColumnOffset).Font.Bold = bBold;
    objCell.get_Offset(iRowOffset, iColumnOffset).HorizontalAlignment = enHAlignment;
}

Cell_OffsetInsertRandomNumbers

private static void Cell_OffsetInsertRandomNumbers(
   Excel.Range objCell,
   int iNoOfRows,
   int iNoOfColumns,
   double dbLowestValue,
   double dbHighestValue,
   int iNoOfDecimals,
   bool bInsertFormula)
{
    int irowcount;
    int icolcount;
    Excel.Range objCell2;
    Random objRandom = new System.Random();

    for (irowcount = 0; irowcount <= iNoOfRows; irowcount++)
    {
        for (icolcount = 0; icolcount <= iNoOfColumns; icolcount++)
        {
            objCell2 = objCell.get_Offset(irowcount, icolcount);
            objCell2.set_Value(System.Reflection.Missing.Value,
                Number_Random(dbLowestValue, dbHighestValue, iNoOfDecimals, objRandom));

            //This line also works
            //objCell.get_Offset(irowcount, icolcount).Value2 = Number_Random(dbLowestValue, dbHighestValue, iNoOfDecimals);
        }
    }
}

Cell_PositionReturn

public static void Cell_PositionReturn(
   Excel.Range objActiveCell, 
   ref float sngFromLeft, 
   ref float sngFromTop)
{
    try
    {

        string scolumnchar;

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

        if (objActiveCell.Column == 1)
        {
            sngFromLeft = 0;
        }
        else
        {
            scolumnchar = clsCol.Letter(objActiveCell.Column - 1);
            sngFromLeft = clsCol.WidthToPoints("A", scolumnchar);
        }

        if (objActiveCell.Row == 1)
        {
            sngFromTop = 0;
        }
        else
        {
            sngFromTop = clsRow.HeightToPoints(1, objActiveCell.Row - 1);
        }

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

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

Cell_SelectCell

public static void Cell_SelectCell(
   params string[] asCellAddresses)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objWorksheet;
        Excel.Range objRange;
        string saddress;

        string scombined;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        foreach (var saddress in asCellAddresses)
            scombined = scombined + saddress + ",";
        scombined = scombined.Substring(0, scombined.Length - 1);

        objRange = (Excel.Range)objWorksheet.Range(scombined);
        objRange.Select();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

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

Cell_TextToColumns

public static void Cell_TextToColumns(
   string sCellAddress, 
   bool bDisplayAlerts = false, 
   string sWshName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objWorksheet;
        Excel.Range objRange;

        if (sWshName.Length == 0)
            objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
        else
            objWorksheet = (Excel.Worksheet)gApplicationExcel.Worksheets(sWshName);

        objRange = (Excel.Range)objWorksheet.Range(sCellAddress);

        gApplicationExcel.DisplayAlerts = bDisplayAlerts;

        objRange.TextToColumns(Destination: objRange, DataType: Excel.XlTextParsingType.xlDelimited, TextQualifier: Excel.XlTextQualifier.xlTextQualifierDoubleQuote, ConsecutiveDelimiter: false, TAB: true, Semicolon: false, Comma: true, Space: false, Other: false, TrailingMinusNumbers: true);

        gApplicationExcel.DisplayAlerts = true;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

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

Cell_TypeReturn

public string Cell_TypeReturn(
   Excel.Range objRange)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        if (objRange.Value == null)
        {
            TypeReturn = "Nothing";
            return;
        }

        switch (System.Convert.ToHexString(objRange.Value))
        {
            case "True":
                {
                    TypeReturn = "TRUE";
                    break;
                }

            case "False":
                {
                    TypeReturn = "FALSE";
                    break;
                }

            case "#DIV/0!":
                {
                    TypeReturn = "#DIV/0!";
                    break;
                }

            case "#N/A":
                {
                    TypeReturn = "#N/A";
                    break;
                }

            case "#NAME?":
                {
                    TypeReturn = "#NAME?";
                    break;
                }

            case "#NULL!":
                {
                    TypeReturn = "#NULL!";
                    break;
                }

            case "NUM!":
                {
                    TypeReturn = "#NUM!";
                    break;
                }

            case "#REF!":
                {
                    TypeReturn = "#REF!";
                    break;
                }

            case "#VALUE!":
                {
                    TypeReturn = "#VALUE!";
                    break;
                }

            default:
                {
                    TypeReturn = System.Convert.ToHexString(objRange.Value);
                    break;
                }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("TypeReturn", msCLASSNAME, "determine the type of the contents in cell: '" + objRange.Address + "'.", mobjCOMException, mobjException);
    }
}

Cell_ValueToDate

public static System.DateTime Cell_ValueToDate(
   string sCellAddress, 
   string sDateFormat)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        System.DateTime dtDateTime;
        Excel.Worksheet objWorksheet;
        Excel.Range objRange;

        string scellcontents;

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

        scellcontents = System.Convert.ToHexString(objRange.Value);

        if (scellcontents == null)
        {
            ValueToDate = DateTime.Now();
            return;
        }

        switch (sDateFormat)
        {
            case "dd.mm.yyyy":
                {
                    if (scellcontents.Length == 10)
                    {
                        dtDateTime = new DateTime(System.Convert.ToInt32(scellcontents.Substring(6, 4)), System.Convert.ToInt32(scellcontents.Substring(3, 2)), System.Convert.ToInt32(scellcontents.Substring(0, 2)));

                        ValueToDate = dtDateTime;
                    }
                    else
                        ValueToDate = DateTime.Now();
                    break;
                }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

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

Cell_ValueToString

public static string Cell_ValueToString(
   string sCellAddress)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objWorksheet;
        Excel.Range objRange;

        string scellcontents;

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

        ValueToString = System.Convert.ToHexString(objRange.Value);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

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

CellRef_GetColFirst

public string CellRef_GetColFirst(string sCompleteReference, bool bIncludeDollar = false)
{
    try
    {
        if (clsError.ErrorFlag())
            return null;

        string sreference = sCompleteReference;
        int icharpos = 0;
        bool bfound = false;

        if (!bIncludeDollar && sreference.StartsWith("$"))
            sreference = sreference.Substring(1);

        while (!bfound && icharpos <= sCompleteReference.Length)
        {
            icharpos++;

            if (icharpos > sreference.Length)
                break;

            char c = sreference[icharpos - 1];

            if (char.IsDigit(c) ||
               (c == '$' && icharpos < sreference.Length && char.IsDigit(sreference[icharpos])))
            {
                bfound = true;
            }
        }

        return sreference.Substring(0, Math.Max(0, icharpos - 1));
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        gobjCOMException = ex;
    }
    catch (Exception ex)
    {
        gobjException = ex;
    }
    finally
    {
        if (gbDEBUG_EXCEL || gobjCOMException != null || gobjException != null)
            clsError.Handle(
                "CellRef_GetColFirst",
                "clsCellRef",
                "return the first column letter from the reference\r\n\r\n'" + sCompleteReference + "'",
                gobjCOMException,
                gobjException
            );
    }

    return null;
}

CellRef_GetColLast

public string CellRef_GetColLast(
   string sCompleteReference, 
   bool bIncludeDollar = false)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        string sreference;
        int icolon;

        sreference = sCompleteReference;
        icolon = InStr(sreference, ":");
        sreference = Strings.Right(sreference, Strings.Len(sreference) - icolon);

        return CellRef_GetColFirst(sreference, bIncludeDollar);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(gobjCOMException) == false | IsNothing(gobjException) == false)))
            clsError.Handle("CellRef_GetColLast", "clsCellRef", 
                  "return the last column letter from the reference" + 
                   Constants.vbCrLf + Constants.vbCrLf + "'" + sCompleteReference + "'", 
                   gobjCOMException, gobjException);
    }
}

CellRef_GetRowFirst

public static string CellRef_RowFirstGet(
   string sCompleteReference, 
   bool bIncludeDollar = false)
{
    RowFirstGet = "";
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        string sreference;
        System.Int32 icolon;
        bool bfound;
        System.Int32 icharpos;

        sreference = sCompleteReference;
        bfound = false;
        if (bIncludeDollar == false)
        {
            if (Microsoft.VisualBasic.InStr(sreference, "$") == 1)
            {
                sreference = Microsoft.VisualBasic.Right(sreference, sreference.Length - 1);
                icharpos = 0;
            }
        }
        while ((bfound == false) & (icharpos <= sreference.Length))
        {
            icharpos = icharpos + 1;
            if (Microsoft.VisualBasic.IsNumeric(Microsoft.VisualBasic.Mid(sreference, icharpos, 1)) == true | ((Microsoft.VisualBasic.Mid(sreference, icharpos, 1) == "$") & (Microsoft.VisualBasic.IsNumeric(Microsoft.VisualBasic.Mid(sreference, icharpos + 1, 1)))) == true)
                bfound = true;
        }                                                              // remove first column
        sreference = Microsoft.VisualBasic.Right(sreference, sreference.Length - icharpos + 1);
        if (bIncludeDollar == false)
        {
            if (Microsoft.VisualBasic.InStr(sreference, "$") == 1)
                sreference = Microsoft.VisualBasic.Right(sreference, sreference.Length - 1);
        }
        icolon = Microsoft.VisualBasic.InStr(sreference, ":");
        if (icolon == 0)
        {
            return sreference;
        }
        else
            return Microsoft.VisualBasic.Left(sreference, icolon - 1);

        if ((icharpos == sCompleteReference.Length))
        {
        }
    }

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

    finally
    {
        if (gbDEBUG_ERRMSG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("CellRef_GetRowFirst", msCLASSNAME, 
                 "return the first row number from the reference" + gsCRLF + gsCRLF + "'" + sCompleteReference + "'", 
                  mobjCOMException, mobjException);
    }
}

CellRef_GetRowLast

public static string CellRef_GetRowLast(
   string sCompleteReference, 
   bool bIncludeDollar = false)
{
    RowLastGet = "";
    try
    {
        if (clsError.ErrorFlag() == true)
        {
            return;
        }
 
        string sreference;
        System.Int32 icolon;

        sreference = sCompleteReference;
        icolon = Microsoft.VisualBasic.InStr(sreference, ":");
        sreference = Microsoft.VisualBasic.Right(sreference, sreference.Length - icolon);

        return clsCellRef.RowFirstGet(sreference, bIncludeDollar);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_ERRMSG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("CellRef_GetRowLast", msCLASSNAME, 
                 "return the last row number from the reference" + gsCRLF + gsCRLF + "'" + sCompleteReference + "'", 
                  mobjCOMException, mobjException);
    }
}

CellRef_HasFolderPath

public bool CellRef_HasFolderPath(
    string sCompleteReference)
{
    try
    {
        if (clsError.ErrorFlag())
            return false;

        int icolon = sCompleteReference.IndexOf(':') + 1;   // VB InStr is 1-based
        int isquarebracketopen = sCompleteReference.IndexOf('[') + 1;

        if (icolon > 0 && icolon < isquarebracketopen)
            return true;
        else
            return false;
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        gobjCOMException = ex;
    }
    catch (Exception ex)
    {
        gobjException = ex;
    }
    finally
    {
        if (gbDEBUG_EXCEL || gobjCOMException != null || gobjException != null)
        {
            clsError.Handle(
                "CellRef_HasFolderPath",
                "clsCellRef",
                "determine if there is a folder path in the reference\r\n'" + sCompleteReference + "'",
                gobjCOMException,
                gobjException
            );
        }
    }

    return false;
}

CellRef_HasRange

public bool CellRef_HasRange(string sCompleteReference)
{
    try
    {
        if (clsError.ErrorFlag())
            return false;

        int icolon = sCompleteReference.IndexOf(':') + 1;   // VB InStr is 1-based

        if (icolon != 0)
            return true;
        else
            return false;
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        gobjCOMException = ex;
    }
    catch (Exception ex)
    {
        gobjException = ex;
    }
    finally
    {
        if (gbDEBUG_EXCEL || gobjCOMException != null || gobjException != null)
        {
            clsError.Handle(
                "CellRef_HasRange",
                "clsCellRef",
                "determine if the reference contains a range of cells or an individual cell\r\n'" +
                sCompleteReference + "'",
                gobjCOMException,
                gobjException
            );
        }
    }

    return false;
}

CellRef_HasWbkName

public bool CellRef_HasWbkName(string sCompleteReference)
{
    try
    {
        if (clsError.ErrorFlag())
            return false;

        int isinglespeechmark = sCompleteReference.IndexOf('\'') + 1; // VB InStr is 1-based
        int isquarebracketopen = sCompleteReference.IndexOf('[') + 1;

        if (isinglespeechmark == 1 && isquarebracketopen == 2)
            return true;
        else
            return false;
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        gobjCOMException = ex;
    }
    catch (Exception ex)
    {
        gobjException = ex;
    }
    finally
    {
        if (gbDEBUG_EXCEL || gobjCOMException != null || gobjException != null)
        {
            clsError.Handle(
                "CellRef_HasWbkName",
                "clsCellRef",
                "determine if there is a workbook component in the reference\r\n'" +
                sCompleteReference + "'",
                gobjCOMException,
                gobjException
            );
        }
    }

    return false;
}

CellRef_HasWshName

public bool CellRef_HasWshName(string sCompleteReference)
{
    try
    {
        if (clsError.ErrorFlag())
            return false;

        int isinglespeechmark = sCompleteReference.IndexOf('\'') + 1; // VB InStr is 1-based
        int iexclamationmark = sCompleteReference.IndexOf('!') + 1;

        if (isinglespeechmark != 0 || iexclamationmark != 0)
            return true;
        else
            return false;
    }
    catch (System.Runtime.InteropServices.COMException ex)
    {
        gobjCOMException = ex;
    }
    catch (Exception ex)
    {
        gobjException = ex;
    }
    finally
    {
        if (gbDEBUG_EXCEL || gobjCOMException != null || gobjException != null)
        {
            clsError.Handle(
                "CellRef_HasWshName",
                "clsCellRef",
                "determine if there is a worksheet component in the reference\r\n'" +
                sCompleteReference + "'",
                gobjCOMException,
                gobjException
            );
        }
    }

    return false;
}

Cells_Clear

public static void Cells_Clear(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

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

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Clear", "clsCells", 
                 "clear the contents of the " + "range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                   mobjCOMException, mobjException);
    }
}

Cells_Copy

public static void Cells_Copy(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

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

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Copy", "clsCells", 
                 "copy the " + "range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                 mobjCOMException, mobjException);
    }
}

Cells_CopyPaste

public static void Cells_CopyPaste(
   long lFromRowFirst, 
   string sFromColFirst, 
   long lFromRowLast = 0, 
   string sFromColLast = "", 
   int iNoOfCols = 0, 
   long lNoOfRows = 0, 
   string sTopLeftCell = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sFromColLast == "")
        {
            sFromColLast = clsCol.Letter(clsCol.Number(sFromColFirst) + iNoOfCols);
        }
        if (lFromRowLast == 0)
        {
            lFromRowLast = lFromRowFirst + lNoOfRows;
        }

        objrange = objworksheet.Range(sFromColFirst + lFromRowFirst + ":" + sFromColLast + lFromRowLast);
        objrange.Copy();

        objrange = objworksheet.Range(sTopLeftCell);
        objrange.Select();

        clszLateBindingExcel.SelectionPaste();

        gApplicationExcel.CutCopyMode = (Excel.XlCutCopyMode)false;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("CopyPaste", "clsCells", 
                 "copy the range " + "'" + sFromColFirst + lFromRowFirst + ":" + sFromColLast + lFromRowLast + 
                  "'" + " and paste to the cell range '" + sTopLeftCell + "'.", mobjCOMException, mobjException);
    }
}

Cells_Cut

public static void Cells_Cut(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

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

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Cut", "clsCells", 
                 "cut the " + "range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                  mobjCOMException, mobjException);
    }
}

Cells_Delete

public static void Cells_Delete(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0, 
   Excel.XlDeleteShiftDirection enShiftDirection = Excel.XlDeleteShiftDirection.xlShiftUp)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        objrange.Delete(Shift: enShiftDirection);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Delete", "clsCells", 
                  "delete the range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + 
                   "' moving the remaining cells '" + enShiftDirection.ToString + "'.", 
                  mobjCOMException, mobjException);
    }
}

Cells_Format

public static void Cells_Format(
   string sFontName, 
   int sngFontSize, 
   int iFontColour, 
   bool bBold, 
   bool bItalic, 
   bool bUnder, 
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        {
            var withBlock = objrange.Font;
            withBlock.Name = sFontName;
            withBlock.Size = sngFontSize;
            withBlock.ColorIndex = iFontColour;
            withBlock.Bold = bBold;
            withBlock.Italic = bItalic;

            if (bUnder == true)
                withBlock.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle;
            if (bUnder == false)
                withBlock.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleNone;
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Format", "clsCells", 
                    "format the cells in the " + "range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                   mobjCOMException, mobjException);
    }
}

Cells_FormatBorderAdd

public static void Cells_FormatBorderAdd(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        {
            var withBlock = objrange;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin;

            withBlock.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin;

            withBlock.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin;

            withBlock.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin;
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("FormatBorderAdd", "clsCells", 
                  "add a border around the " + "range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                   mobjCOMException, mobjException);
    }
}

Cells_FormatBorderAddTopBottom

public static void Cells_FormatBorderAddTopBottom(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        {
            var withBlock = objrange;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin;

            withBlock.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin;
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("FormatBorderAddTopBottom", "clsCells", 
                    "add a border to the top and bottom of the " + "range '" + 
                    sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                     mobjCOMException, mobjException);
    }
}

Cells_FormatBordersClear

public static void Cells_FormatBordersClear(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;
        Excel.Border objborder;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        {
            var withBlock = objrange;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            withBlock.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            withBlock.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            withBlock.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
        {
            if (lNoOfRows > 0)
                lRowLast = lRowFirst + lNoOfRows;

            clsError.Handle("FormatBordersClear", "clsCells", 
                     "clear all the borders from the cells in the " + "range '" + 
                    sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                   mobjCOMException, mobjException);
        }
    }
}

Cells_FormatInterior

public static void Cells_FormatInterior(
   int iColorIndex, 
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0)
{
    try
    {
        Excel.Worksheet objworksheet;
        Excel.Range objrange;

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

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        {
            var withBlock = objrange.Interior;
            withBlock.ColorIndex = iColorIndex;
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("FormatInterior", "clsCells", 
                     "format the interior of the cells in the " + "range '" + 
                      sColFirst + lRowFirst + ":" + sColLast + lRowLast + "'.", 
                      mobjCOMException, mobjException);
    }
}

Cells_Insert

public static void Cells_Insert(
   string sColFirst, 
   long lRowFirst, 
   string sColLast = "", 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0, 
   Excel.XlInsertShiftDirection enShiftDirection = Excel.XlInsertShiftDirection.xlShiftDown)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows;

        objrange = objworksheet.Range(sColFirst + lRowFirst + ":" + sColLast + lRowLast);

        objrange.Insert(Shift: enShiftDirection);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("Insert", "clsCells", 
                        "insert the range '" + sColFirst + lRowFirst + ":" + sColLast + lRowLast + 
                         "' moving the existing cells '" + enShiftDirection.ToString + "'.", 
                          mobjCOMException, mobjException);
    }
}

Cells_RecordMove

public static int Cells_RecordMove(
   ref string sPositiveOrNegativeChange, 
   System.Windows.Forms.VScrollBar vsbLineMove, 
   string sColFirst, 
   int iRowFirst, 
   string sColLast, 
   int iRowLast, 
   int iRowNoActive, 
   int lNoOfRowsInSelection, 
   int lCurrentRowInSelection, 
   int iNextVisibleRow, 
   int iPreviousVisibleRow, 
   int iColorIndex = 15)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objWorksheet;
        Excel.Range objRange;

        if (vsbLineMove.Value == 0)
            vsbLineMove.Value = vsbLineMove.Value + 1;
        if (vsbLineMove.Value == 0)
            return;

        // make the dialog box modeless and select the cell in the relevant row

        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
        objRange = (Excel.Range)objWorksheet.Range(sColFirst + iRowNoActive + ":" + sColLast + iRowNoActive);
        objRange.Interior.ColorIndex = Excel.XlColorIndex.xlColorIndexNone;

        sPositiveOrNegativeChange = "";

        if ((vsbLineMove.Value > lCurrentRowInSelection))
        {
            if ((iRowNoActive) >= iRowFirst & (iRowNoActive + 1) <= iRowLast)
            {
                if ((iNextVisibleRow <= iRowLast))
                    iRowNoActive = iNextVisibleRow;

                sPositiveOrNegativeChange = "Positive";
            }
            else
                vsbLineMove.Value = vsbLineMove.Value - 1;
        }
        else if ((vsbLineMove.Value < lCurrentRowInSelection))
        {
            if ((iRowNoActive - 1) >= iRowFirst & (iRowNoActive) <= iRowLast)
            {
                if ((iPreviousVisibleRow >= iRowFirst))
                    iRowNoActive = iPreviousVisibleRow;
                else
                    vsbLineMove.Value = vsbLineMove.Value - 1;

                sPositiveOrNegativeChange = "Negative";
            }
            else
                vsbLineMove.Value = vsbLineMove.Value + 1;
        }

        objRange = (Excel.Range)objWorksheet.Range(sColFirst + iRowNoActive + ":" + sColLast + iRowNoActive);
        objRange.Interior.ColorIndex = iColorIndex;

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

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

Cells_RemoveDuplicatesInColumn

public static void Cells_RemoveDuplicatesInColumn(
   string sDuplicateCol, 
   string sColFirst, 
   Int64 i64RowFirst, 
   string sColLast = "", 
   Int64 i64RowLast = 0, 
   int iNoOfCols = 0, 
   Int64 i64NoOfRows = 0, 
   bool bTopDuplicate = true)
{
    Excel.Worksheet objworksheet;
    Excel.Range objrange1;
    Excel.Range objrange2;

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

        Int64 i64rownumber;
        Int64 i64columnnumber;
        Int64 i64columnduplicate;
        int icolumnfirst;
        int icolumnlast;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (i64RowLast == 0)
            i64RowLast = i64RowFirst + i64NoOfRows;

        i64columnduplicate = clsCol.Number(sDuplicateCol);

        icolumnfirst = clsCol.Number(sColFirst);
        icolumnlast = clsCol.Number(sColLast);

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        for (i64rownumber = i64RowLast; i64rownumber <= (i64RowFirst + 1); i64rownumber++)
        {
            objrange1 = (Excel.Range)objworksheet.Range(sDuplicateCol + i64rownumber);
            objrange2 = (Excel.Range)objworksheet.Range(sDuplicateCol + i64rownumber - 1);

            if (System.Convert.ToHexString(objrange1.Value) == System.Convert.ToHexString(objrange2.Value))
            {
                if (bTopDuplicate == true)
                    clsCells.Delete(sColFirst, i64rownumber, sColLast, i64rownumber - 1, null/* Conversion error: Set to default value for this argument */, null/* Conversion error: Set to default value for this argument */, Excel.XlDeleteShiftDirection.xlShiftUp);

                if (bTopDuplicate == false)
                    clsCells.Delete(sColFirst, i64rownumber, sColLast, i64rownumber, null/* Conversion error: Set to default value for this argument */, null/* Conversion error: Set to default value for this argument */, Excel.XlDeleteShiftDirection.xlShiftUp);
            }
        }
    }

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

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

Cells_ToArray

public static void Cells_ToArray(
   string sArrayname, 
   ref string[,] asArrayName, 
   int iColFirst, 
   long lRowFirst, 
   int iColLast = 0, 
   long lRowLast = 0, 
   int iNoOfCols = 0, 
   long lNoOfRows = 0, 
   string sWshName = "")
{

    // need a column to array for a 1-dimensional array

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

        Excel.Worksheet objworksheet;
        Excel.Range objrangestart;
        Excel.Range objrangefinish;
        Excel.Range objrange;

        int icolumncounter;
        long lrowcounter;

        if (iColLast == 0)
            iColLast = iColFirst + iNoOfCols - 1;
        if (lRowLast == 0)
            lRowLast = lRowFirst + lNoOfRows - 2;

        asArrayName = new string[System.Convert.ToInt32(lRowLast - lRowFirst) + 1, iColLast - iColFirst + 1];

        // Call MsgBox("Redim Array" & vbCrLf & _
        // "First dimension : " & iColLast - iColFirst & vbCrLf & _
        // "Second dimension : " & CType(lRowLast - lRowFirst, Integer))

        if (sWshName.Length == 0)
            objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
        else
            objworksheet = (Excel.Worksheet)gApplicationExcel.Worksheets(sWshName);

        for (icolumncounter = iColFirst; icolumncounter <= iColLast; icolumncounter++)
        {
            for (lrowcounter = lRowFirst; lrowcounter <= lRowLast; lrowcounter++)
            {
                objrange = (Excel.Range)objworksheet.Cells(lrowcounter, icolumncounter);

                asArrayName[System.Convert.ToInt32(lrowcounter - lRowFirst), icolumncounter - iColFirst] = 
                  System.Convert.ToHexString(objrange.Value);
            }
        }
    }


    // objrangestart = CType(objworksheet.Cells(lRowFirst, iColFirst), _
    // Microsoft.Office.Interop.Excel.Range)
    // objrangefinish = CType(objworksheet.Cells(lRowLast, iColLast), _
    // Microsoft.Office.Interop.Excel.Range)

    // asArrayName = CType(objworksheet.Range(objrangestart, objrangefinish).Value, String(,))

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

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("ToArray", "clsCells", 
                    "copy the range '" + iColFirst + lRowFirst + ":" + iColLast + lRowLast + 
                     "' into the array '" + sArrayname + "'", 
                      mobjCOMException, mobjException);
    }
}

Cells_ToArrayLetters

public static void Cells_ToArrayLetters(
   string sArrayname, 
   ref string[,] asArrayName, 
   string sColFirst, 
   int iRowFirst, 
   string sColLast, 
   int iRowLast, 
   int iNoOfCols = 0, 
   int iNoOfRows = 0)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        int icolumnnumber;
        int irownumber;
        int icolumnfirst;
        int icolumnlast;

        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (iRowLast == 0)
            iRowLast = iRowFirst + iNoOfRows;
        icolumnfirst = clsCol.Number(sColFirst);
        icolumnlast = clsCol.Number(sColLast);

        asArrayName = new string[icolumnlast - icolumnfirst + 1, iRowLast - iRowFirst + 1];

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        for (icolumnnumber = icolumnfirst; icolumnnumber <= icolumnlast; icolumnnumber++)
        {
            for (irownumber = iRowFirst; irownumber <= iRowLast; irownumber++)
            {
                objrange = (Excel.Range)objworksheet.Cells(irownumber, icolumnnumber);

                asArrayName[icolumnnumber - icolumnfirst, irownumber - iRowFirst] = System.Convert.ToHexString(objrange.Value);
            }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("ToArrayLetters", "clsCells", 
                     "copy the range '" + sColFirst + iRowFirst + ":" + sColLast + iRowLast + "??", 
                      mobjCOMException, mobjException);
    }
}

Cells_ToDataTable

public static void Cells_ToDataTable(
   System.Data.DataSet objDataSet, 
   string sTableName, 
   string sColFirst, 
   int iRowFirst, 
   string sColLast, 
   int iRowLast, 
   int iNoOfCols = 0, 
   int iNoOfRows = 0)
{
    Excel.Worksheet objworksheet;
    Excel.Range objrange;
    System.Data.DataRow objDataRow;

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

        int irownumber;
        int icolumnnumber;
        int icolumnfirst;
        int icolumnlast;

        if (sColLast == "")
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        if (iRowLast == 0)
            iRowLast = iRowFirst + iNoOfRows;
        icolumnfirst = clsCol.Number(sColFirst);
        icolumnlast = clsCol.Number(sColLast);

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        for (irownumber = iRowFirst; irownumber <= iRowLast; irownumber++)
        {
            objDataRow = clsDataSet.mobjDataSet.Tables(sTableName).NewRow;

            for (icolumnnumber = icolumnfirst; icolumnnumber <= icolumnlast; icolumnnumber++)
            {
                objrange = (Excel.Range)objworksheet.Cells(irownumber, icolumnnumber);

                objDataRow.Item[icolumnfirst - icolumnnumber] = (string)objrange.Value;
            }

            objDataSet.Tables[sTableName].Rows.Add(objDataRow);
        }
    }

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

    finally
    {
        objworksheet = null/* TODO Change to default(_) if this is not a reference type */;
        objrange = null/* TODO Change to default(_) if this is not a reference type */;

        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("ToDataTable", "clsCells", 
                    "transfer the range '" + sColFirst + iRowFirst + ":" + sColLast + iRowLast + 
                      "' to the datatable '" + sTableName + "'" + " in the dataset '" + objDataSet.ToString + "'.", 
                     mobjCOMException, mobjException);
    }
}

Cells_ToListBox

public static void Cells_ToListBox(
   System.Windows.Forms.ListBox objListBox, 
   string sColFirst, 
   int iRowFirst, 
   string sColLast, 
   int iRowLast, 
   bool bIncludeTopBlank = false, 
   int iColumnNo = 0, 
   bool bUniqueItems = false, 
   string sWshName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        int icolumnnumber;
        int irownumber;
        string swshname_before;

        Excel.Worksheet objworksheetbefore;
        Excel.Worksheet objworksheet;
        Excel.Range objrange;

        if ((sWshName.Length > 0))
        {
            objworksheetbefore = (Excel.Worksheet)gApplicationExcel.ActiveSheet;
            swshname_before = objworksheetbefore.Name;

            objworksheet = (Excel.Worksheet)gApplicationExcel.Worksheets(sWshName);
        }
        if ((sWshName.Length == 0))
            objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        for (icolumnnumber = clsCol.Number(sColFirst); icolumnnumber <= clsCol.Number(sColLast); icolumnnumber++)
        {
            for (irownumber = iRowFirst; irownumber <= iRowLast; irownumber++)
            {
                objrange = (Excel.Range)objworksheet.Cells(irownumber, icolumnnumber);
                objListBox.Items.Add(objrange.Value);
            }
        }

        if (bIncludeTopBlank == true)
            objListBox.Items.Add("");
        if ((sWshName.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)))
            clsError.Handle("ToListBox", "clsCells", 
                    "transfer the range '" + sColFirst + iRowFirst + ":" + sColLast + iRowLast + "' to the listbox '" + 
                     objListBox.ToString + "'", mobjCOMException, mobjException);
    }
}

Cells_ToListComboBox

public static void Cells_ToListComboBox(
   System.Windows.Forms.ListControl lstBoxName, 
   string sColFirst, 
   int iRowFirst, 
   string sColLast, 
   int iRowLast)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        lstBoxName.DataSource = sColFirst + iRowFirst + ":" + sColLast + iRowLast;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(gobjCOMException) == false | IsNothing(gobjException) == false)))
            clsError.Handle("ToListComboBox", "clsWsh", 
                   "assign the source for the listbox '" + lstBoxName.ToString + "' to be the range " + "'" + 
                    sColFirst + iRowFirst + ":" + sColLast + iRowLast + "'.", 
                    gobjCOMException, gobjException);
    }
}

Cells_ToListView

public static void Cells_ToListView(
   System.Windows.Forms.ListView objListView, 
   string sColFirst, 
   int iRowFirst, 
   string sColLast, 
   int iRowLast, 
   int iNoOfCols = 0, 
   int iNoOfRows = 0)
{
    Excel.Worksheet objworksheet;
    Excel.Range objrange;
    System.Windows.Forms.ListViewItem objlistviewitem;

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

        int irownumber;
        int icolumnnumber;
        int icolumnfirst;
        int icolumnlast;

        if (sColLast == "")
        {
            sColLast = clsCol.Letter(clsCol.Number(sColFirst) + iNoOfCols);
        }
        if (iRowLast == 0)
        {
            iRowLast = iRowFirst + iNoOfRows;
        }
        icolumnfirst = clsCol.Number(sColFirst);
        icolumnlast = clsCol.Number(sColLast);

        objworksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        for (irownumber = iRowFirst; irownumber <= iRowLast; irownumber++)
        {
            objrange = (Excel.Range)objworksheet.Cells(irownumber, icolumnnumber);

            objlistviewitem = objListView.Items.Add(System.Convert.ToHexString(objrange.Value));

            for (icolumnnumber = icolumnfirst; icolumnnumber <= icolumnlast; icolumnnumber++)
            {
                objrange = (Excel.Range)objworksheet.Cells(irownumber, icolumnnumber);

                objlistviewitem.SubItems.Add(System.Convert.ToHexString(objrange.Value));
            }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("ToListView", "clsCells", 
                    "copy the range '" + sColFirst + iRowFirst + ":" + sColLast + iRowLast + 
                     "to the listview '" + objListView.ToString + "'.", 
                    mobjCOMException, mobjException);
    }
}

Message_RangeNoNumericalData

public static void Message_RangeNoNumericalData(string sRangeAddress)
{
    System.Windows.Forms.MessageBox.Show(
        "The range '" + sRangeAddress + "' does not include any numerical data.",
        gsDIALOG_PREFIX_EXCEL,
        System.Windows.Forms.MessageBoxButtons.OK,
        System.Windows.Forms.MessageBoxIcon.Information
    );
}

Range_ColumnFirst

public static string Range_ColumnFirst(
   Excel.Range objRange)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        ColumnFirst = clsCol.Letter(objRange.Column);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(gobjCOMException) == false | IsNothing(gobjException) == false)))
            clsError.Handle("ColumnFirst", "clsRange", 
               "return the column letter for the first column in the range " + 
               "'" + objRange.Address + "'.", gobjCOMException, gobjException);
    }
}

Range_ColumnLast

public static string Range_ColumnLast(
   Excel.Range objRange)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        ColumnLast = clsCol.Letter(objRange.Column + objRange.Columns.Count - 1);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(gobjCOMException) == false | IsNothing(gobjException) == false)))
            clsError.Handle("ColumnLast", "clsRange", 
                "return the column letter for the last column in the range " 
                 + "'" + objRange.Address + "'.", gobjCOMException, gobjException);
    }
}

Range_ConcatenateText

public static void Range_ConcatenateText(
   Excel.Range objRange, 
   string sFromDirection = "Right", 
   bool bIncludeSpaces = true, 
   bool bClearCellContents = true)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objCellRange;
        Excel.Range objCellCurrent;

        System.Int32 icellcount;
        string scellcurrent = "";
        string sconcat = "";

        for (icellcount = 1; icellcount <= objRange.Count; icellcount++)
        {
            objCellRange = (Excel.Range)objRange.Cells(icellcount);

            sconcat = clsCell.ValueToString(objCellRange);

            objCellCurrent = (Excel.Range)objRange.Cells(icellcount);

            // offset 1 is from the right
            objCellCurrent = objCellCurrent.Offset(0, 1);
            scellcurrent = clsCell.ValueToString(objCellCurrent);

            while ((scellcurrent.Length > 0))
            {
                if (bIncludeSpaces == true)
                    sconcat = sconcat + " ";
                sconcat = sconcat + scellcurrent;

                objCellCurrent = objCellCurrent.Offset(0, 1);
                scellcurrent = clsCell.ValueToString(objCellCurrent);
            }

            objCellRange.Value = sconcat;
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_ERRMSG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("ConcatenateText", msCLASSNAME, 
                 "concatenate the text from the cells on the left in the range '" + 
                 objRange.Address + "'", mobjCOMException, mobjException);
    }
}

Range_HasNumericalData

public static bool Range_HasNumericalData(
   Excel.Range objRange, 
   bool bInformUser = false)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        int icellcount;
        Excel.Range objcell;
        bool bnumeric = false;

        for (icellcount = 1; icellcount <= objRange.Cells.Count; icellcount++)
        {
            objcell = (Excel.Range)objRange.Item(icellcount);

            if (IsNumeric(objcell.Value) == true)
            {
                bnumeric = true;
                break;
            }
        }

        if (bnumeric == false & bInformUser == true)
            clszMessagesExcel.RangeNoNumericalDataInformation(objRange.Address);

        HasNumericalData = bnumeric;
    }
    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("HasNumericalData", "clsRange", "determine if the range '" + 
               objRange.Address + "'" + " contains any numerical data.", mobjCOMException, mobjException);
    }
}

Range_OffSetValueToString

public static string Range_OffSetValueToString(
   Excel.Range objRange, 
   System.Int32 iOffsetRows, 
   System.Int32 iOffsetColumns, 
   bool bReplaceSpaces = false, 
   bool bLowerCase = false)
{
    OffSetValueToString = "";
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objCell;

        objCell = (Excel.Range)objRange.Offset(iOffsetRows, iOffsetColumns);

        OffSetValueToString = System.Convert.ToHexString(objCell.Value);

        if (bReplaceSpaces == true)
            OffSetValueToString = OffSetValueToString.Replace(" ", "");

        if (bLowerCase == true)
            OffSetValueToString = OffSetValueToString.ToLower;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

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

Range_PasteSpecial

public static void Range_PasteSpecial(
   Excel.XlPasteType enPasteType, 
   Excel.XlPasteSpecialOperation enSpecialOperation, 
   bool bTranspose = false, 
   bool bSkipBlanks = false)
{
    Excel.Range objRange;
    Excel.Worksheet objWorksheet;

    try
    {
        objRange = (Excel.Range)gApplicationExcel.Selection;

        objRange.PasteSpecial(Paste: enPasteType, Operation: enSpecialOperation, SkipBlanks: bSkipBlanks, Transpose: bTranspose);
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        objWorksheet = (Excel.Worksheet)gApplicationExcel.ActiveSheet;

        clszForceErrorsExcel.WorksheetPaste(objWorksheet);
    }

    finally
    {
        objRange = null/* TODO Change to default(_) if this is not a reference type */;
        objWorksheet = null/* TODO Change to default(_) if this is not a reference type */;
    }
}

Range_PasteSpecialText

public static bool Range_PasteSpecialText()
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        PasteSpecialText = clszForceErrorsExcel.RangePasteSpecialText();
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

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

Range_RemoveSpaces

public static void Range_RemoveSpaces(
   Excel.Range objRange, 
   System.Int32 iNumberOfSpaces = -1, 
   bool bAlwaysRemove = false)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Range objCellRange;

        System.Int32 icellcount;
        string scellcontents;

        for (icellcount = 1; icellcount <= objRange.Count; icellcount++)
        {
            objCellRange = (Excel.Range)objRange.Cells(icellcount);
            scellcontents = System.Convert.ToHexString(objCellRange.Value);

            if ((!scellcontents == null))
            {
                if ((iNumberOfSpaces == -1) & (icellcount == 1))
                    iNumberOfSpaces = (scellcontents.Length - scellcontents.TrimStart().Length);

                if ((scellcontents.Length > iNumberOfSpaces) & (iNumberOfSpaces > 1))
                {
                    if (bAlwaysRemove == true)
                        objCellRange.Value = scellcontents.Substring(iNumberOfSpaces);
                    else if (((scellcontents.Length - scellcontents.TrimStart().Length) >= iNumberOfSpaces))
                        objCellRange.Value = scellcontents.Substring(iNumberOfSpaces);
                    else
                        objCellRange.Value = scellcontents.TrimStart();
                }
            }
            else
            {
            }
        }
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }

    finally
    {
        if (gbDEBUG_ERRMSG_EXCEL == true | ((!mobjCOMException == null) | (!mobjException == null)))
            clsError.Handle("RemoveSpaces", msCLASSNAME, 
                  "remove the spaces on the LEFT from the range'" + objRange.Address + "'", mobjCOMException, mobjException);
    }
}

Range_RowFirst

public static int Range_RowFirst(
   Excel.Range objRange)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        RowFirst = objRange.Row;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(gobjCOMException) == false | IsNothing(gobjException) == false)))
            clsError.Handle("RowFirst", "clsRange", 
                   "return the row number for the first row in the range " + 
                     "'" + objRange.Address + "'.", gobjCOMException, gobjException);
    }
}

Range_RowLast

public static int Range_RowLast(
   Excel.Range objRange)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        RowLast = objRange.Row + objRange.Rows.Count - 1;
    }
    catch (Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }

    finally
    {
        if (gbDEBUG_EXCEL == true | ((IsNothing(gobjCOMException) == false | IsNothing(gobjException) == false)))
            clsError.Handle("RowLast", "clsRange", 
                   "return the row number for the last row in the range " + 
                   "'" + objRange.Address + "'.", gobjCOMException, gobjException);
    }
}

---

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