C# - Snippets

Message_DoesNotExist

public static void Message_NamedRangeDoesNotExist(string sNamedRangeName)
{
    string sMessage =
        "This workbook named range does not exist:" + 
        Environment.NewLine + Environment.NewLine +
        "'" + sNamedRangeName + "'";

    System.Windows.Forms.MessageBox.Show(
        sMessage,
        modConstants.g_sCOMPANYNAME + " (" + g_sVERSION + ") - Named Range",
        System.Windows.Forms.MessageBoxButtons.OK,
        System.Windows.Forms.MessageBoxIcon.Information
    );

    Tracer_Add("MESSAGE", sMessage);
}

Wbk_NamedRangeConstantAdd

public static void Wbk_NamedRangeConstantAdd(
    string sNamedRange,
    string sConstantValue,
    bool bVisible = false,
    bool bCheckExists = true)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        if (bCheckExists == true)
        {
            if (clsWbk.NamedRangeExists(sNamedRange) == true)
            {
                clsWbk.NamedRangeConstantSet(sNamedRange, sConstantValue);
                return;
            }
        }

        gApplicationExcel.ActiveWorkbook.Names.Add(
            Name: sNamedRange,
            RefersToR1C1: "=" + sConstantValue,
            Visible: bVisible
        );
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (bVisible == true)
                serrortext = "'Visible'";
            else
                serrortext = "'Hidden'";

            clsError.Handle(
                "NamedRangeConstantAdd",
                "clsWbk",
                "add the " + serrortext + " named range '" + sNamedRange +
                "' with the constant value '" + sConstantValue + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Wbk_NamedRangeConstantSet

public static void Wbk_NamedRangeConstantSet(
    string sNamedRange,
    string sConstantValue,
    string sWshName = "")
{
    Excel.Workbook objWorkbook;

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

        int inamescounter;

        objWorkbook = gApplicationExcel.ActiveWorkbook;

        for (inamescounter = 1; inamescounter <= objWorkbook.Names.Count; inamescounter++)
        {
            if (objWorkbook.Names.Item(inamescounter).Name == sNamedRange)
            {
                objWorkbook.Names.Item(inamescounter).RefersToR1C1 = "=" + sConstantValue;
                break;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NamedRangeConstantSet",
                "clsWbk",
                "change the contents of the named range '" + sNamedRange + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Wbk_NamedRangeDeleteLoopPrefix

public static bool Wbk_NamedRangeDelete(
    string sNamedRange,
    string sWshName,
    string sWbkName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return false;

        Excel.Workbook objWorkbook;
        Excel.Names objNames;
        Excel.Name objName;

        int inamescount;
        bool bfound = false;

        // need to add the same for workbooks

        objWorkbook = gApplicationExcel.ActiveWorkbook;

        objNames = objWorkbook.Names;

        for (inamescount = 1; inamescount <= objNames.Count; inamescount++)
        {
            objName = objNames.Item(inamescount);

            if (objName.Name == sWshName + "!" + sNamedRange)
            {
                objName.Delete();
                bfound = true;
                break;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NamedRangeDelete",
                msCLASSNAME,
                "delete the 'worksheet' named range '" + sNamedRange + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return false;
}

Wbk_NamedRangeExists

public static bool Wbk_NamedRangeExists(
    string sNamedRange,
    string sWshName = "",
    string sWbkName = "")
{
    Excel.Workbook objWorkbook = null;
    Excel.Names objNames = null;
    Excel.Name objName = null;

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

        int inamescount;
        bool bfound = false;

        objWorkbook = gApplicationExcel.ActiveWorkbook;
        objNames = objWorkbook.Names;

        for (inamescount = 1; inamescount <= objNames.Count; inamescount++)
        {
            objName = objNames.Item(inamescount);

            if (objName.Name == sNamedRange)
            {
                bfound = true;
                break;
            }
        }

        return bfound;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        gobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        gobjException = objException;
    }
    finally
    {
        objWorkbook = null;
        objNames = null;
        objName = null;

        if (gbDEBUG_EXCEL == true ||
            (gobjCOMException != null || gobjException != null))
        {
            clsError.Handle(
                "NamedRangeExists",
                "clsWbk",
                "determine if the 'workbook' named range '" + sNamedRange + "' exists.",
                gobjCOMException,
                gobjException
            );
        }
    }

    return false;
}

Wbk_NamedRangeGet

public static string Wbk_NamedRangeGet(
    string sNamedRange,
    bool bRemoveEquals = true)
{
    Excel.Workbook objWorkbook;

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

        int inamescounter;

        objWorkbook = gApplicationExcel.ActiveWorkbook;

        string NamedRangeGet = "";

        for (inamescounter = 1; inamescounter <= objWorkbook.Names.Count; inamescounter++)
        {
            if (objWorkbook.Names.Item(inamescounter).Name == sNamedRange)
            {
                NamedRangeGet = objWorkbook.Names.Item(inamescounter).Value;
                break;
            }
        }

        if (bRemoveEquals == true && NamedRangeGet.Length > 0)
        {
            // VB: Substring(1, length-1) ? C#: Substring(1)
            NamedRangeGet = NamedRangeGet.Substring(1);
        }

        return NamedRangeGet;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NamedRangeGet",
                "clsWbk",
                "return the contents of the named range '" + sNamedRange + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return "";
}

Wsh_NamedRangeConstantAdd

public static void Wsh_NamedRangeConstantAdd(
    string sWshName,
    string sNamedRange,
    string sConstantValue,
    bool bVisible = false,
    bool bHidden = false)
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return;

        Excel.Worksheet objWorksheet;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.Worksheets[sWshName];

        objWorksheet.Names.Add(
            Name: sNamedRange,
            RefersToR1C1: "=" + sConstantValue,
            Visible: bVisible
        );
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            string serrortext;

            if (bHidden == true)
                serrortext = "'Hidden'";
            else
                serrortext = "";

            clsError.Handle(
                "NamedRangeConstantAdd",
                msCLASSNAME,
                "add the " + serrortext + " named range '" + sNamedRange +
                "' with the constant value '" + sConstantValue + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Wsh_NamedRangeConstantSet

public static void Wsh_NamedRangeConstantSet(
    string sWshName,
    string sNamedRange,
    string sConstantValue)
{
    Excel.Worksheet objWorksheet;

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

        int inamescounter;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.Worksheets[sWshName];

        for (inamescounter = 1; inamescounter <= objWorksheet.Names.Count; inamescounter++)
        {
            if (objWorksheet.Names.Item(inamescounter).Name == sNamedRange)
            {
                objWorksheet.Names.Item(inamescounter).RefersToR1C1 = "=" + sConstantValue;
                break;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NamedRangeConstantSet",
                msCLASSNAME,
                "change the contents of the named range '" + sNamedRange + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }
}

Wsh_NamedRangeDelete

public static bool Wsh_NamedRangeDelete(
    string sNamedRange,
    string sWshName,
    string sWbkName = "")
{
    try
    {
        if (clsError.ErrorFlag() == true)
            return false;

        Excel.Worksheet objWorksheet;
        Excel.Names objNames;
        Excel.Name objName;

        int inamescount;
        bool bfound = false;

        // need to add the same for workbooks

        objWorksheet = (Excel.Worksheet)gApplicationExcel.Worksheets[sWshName];

        objNames = objWorksheet.Names;

        for (inamescount = 1; inamescount <= objNames.Count; inamescount++)
        {
            objName = objNames.Item(inamescount);

            if (objName.Name == sWshName + "!" + sNamedRange)
            {
                objName.Delete();
                bfound = true;
                break;
            }
        }
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NamedRangeDelete",
                msCLASSNAME,
                "delete the 'worksheet' named range '" + sNamedRange + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return false;
}

Wsh_NamedRangeGet

public static string Wsh_NamedRangeGet(
    string sWshName,
    string sNamedRange,
    bool bRemoveEquals = true)
{
    Excel.Worksheet objWorksheet;

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

        int inamescounter;

        objWorksheet = (Excel.Worksheet)gApplicationExcel.Worksheets[sWshName];

        string NamedRangeGet = "";

        for (inamescounter = 1; inamescounter <= objWorksheet.Names.Count; inamescounter++)
        {
            if (objWorksheet.Names.Item(inamescounter).Name == sNamedRange)
            {
                NamedRangeGet = objWorksheet.Names.Item(inamescounter).Value;
                break;
            }
        }

        if (bRemoveEquals == true && NamedRangeGet.Length > 0)
        {
            // VB: Substring(1, length-1) ? C#: Substring(1)
            NamedRangeGet = NamedRangeGet.Substring(1);
        }

        return NamedRangeGet;
    }
    catch (System.Runtime.InteropServices.COMException objCOMException)
    {
        mobjCOMException = objCOMException;
    }
    catch (Exception objException)
    {
        mobjException = objException;
    }
    finally
    {
        if (gbDEBUG_EXCEL == true ||
            (mobjCOMException != null || mobjException != null))
        {
            clsError.Handle(
                "NamedRangeGet",
                msCLASSNAME,
                "return the contents of the named range '" + sNamedRange + "'.",
                mobjCOMException,
                mobjException
            );
        }
    }

    return "";
}