C# Snippets

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 (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'";
            if (bVisible == false)
                serrortext = "'Hidden'";

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

Public Shared Sub Wbk_NamedRangeConstantAdd(ByVal sNamedRange As String, _
                                        ByVal sConstantValue As String, _
                               Optional ByVal bVisible As Boolean = False, _
                               Optional ByVal bCheckExists As Boolean = True)

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

      If bCheckExists = True Then
         If clsWbk.NamedRangeExists(sNamedRange) = True Then
            Call clsWbk.NamedRangeConstantSet(sNamedRange, sConstantValue)
            Exit Sub
         End If
      End If

      gApplicationExcel.ActiveWorkbook.Names.Add _
         (Name:=sNamedRange, _
          RefersToR1C1:="=" & sConstantValue, _
          Visible:=bVisible)

   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 bVisible = True Then serrortext = "'Visible'"
         If bVisible = False Then serrortext = "'Hidden'"

         Call clsError.Handle("NamedRangeConstantAdd", "clsWbk", _
              "add the " & serrortext & " named range '" & sNamedRange & "' " & _
              "with the constant value '" & sConstantValue & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

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 (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);
    }
}

Public Shared Sub Wbk_NamedRangeConstantSet(ByVal sNamedRange As String, _
                                        ByVal sConstantValue As String, _
                               Optional ByVal sWshName As String = "")

   Dim objworkbook As Excel.Workbook

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

      Dim inamescounter As Integer

      objworkbook = gApplicationExcel.ActiveWorkbook

      For inamescounter = 1 To objworkbook.Names.Count
         If objworkbook.Names.Item(inamescounter).Name = sNamedRange Then
            objworkbook.Names.Item(inamescounter).RefersToR1C1 = "=" & sConstantValue
            Exit For
         End If
      Next inamescounter

   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("NamedRangeConstantSet", "clsWbk", _
              "change the contents of the named range '" & sNamedRange & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try

End Sub

Wbk_NamedRangeDeleteLoopPrefix

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

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

        int inamescount;
        bool bfound;

        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();
                break;
            }
        }
    }
    catch (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);
    }
}

Public Shared Function Wbk_NamedRangeDelete(ByVal sNamedRange As String, _
                                        ByVal sWshName As String, _
                               Optional ByVal sWbkName As String = "") _
                                        As Boolean

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

      Dim objworkbook As Excel.Workbook
      Dim objNames As Excel.Names
      Dim objName As Excel.Name

      Dim inamescount As Integer
      Dim bfound As Boolean

      bfound = False

      'need to add the same for workbooks

      objWorkbook = gApplicationExcel.ActiveWorkbook

      objNames = objworkbook.Names

      For inamescount = 1 To objNames.Count
         objName = objNames.Item(inamescount)

         If objName.Name = sWshName & "!" & sNamedRange Then
            objName.Delete()
            Exit For
         End If
      Next

   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("NamedRangeDelete", msCLASSNAME, _
              "delete the 'worksheet' named range '" & sNamedRange & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function

Wbk_NamedRangeExists

public static bool Wbk_NamedRangeExists(
    Excel.Workbook workbook,
    string namedRange)
{
    try
    {
        if (workbook == null || namedRange == null)
        {
            return false;
        }
        foreach (Excel.Name name in workbook.Names)
        {
            if (name == null || name.Name == null)
            {
                continue;
            }

            if (name.Name.ToLower().Equals(namedRange.ToLower()))
            {
                return true;
            }
        }
        return false;
    }
    catch (System.Exception ex)
    {
        ExceptionHandling.MessageShow(System.Reflection.MethodBase.GetCurrentMethod(), ex);
        return false;
    }
}

Public Shared Function Wbk_NamedRangeExists(ByVal sNamedRange As String, _
                               Optional ByVal sWshName As String = "", _
                               Optional ByVal sWbkName As String = "") _
                                        As Boolean

   Dim objWorkbook As Excel.Workbook
   Dim objNames As Excel.Names
   Dim objName As Excel.Name

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

      Dim inamescount As Integer
      Dim bfound As Boolean

      bfound = False
      objWorkbook = gApplicationExcel.ActiveWorkbook
      objNames = objWorkbook.Names
      For inamescount = 1 To objNames.Count
         objName = objNames.Item(inamescount)

         If objName.Name = sNamedRange Then
            bfound = True
            Exit For
         End If
      Next
      NamedRangeExists = bfound

   Catch objCOMException As System.Runtime.InteropServices.COMException
      gobjCOMException = objCOMException
   Catch objException As Exception
      gobjException = objException

   Finally
      objWorkbook = Nothing
      objNames = Nothing
      objName = Nothing

      If gbDEBUG_EXCEL = True Or _
         ((IsNothing(gobjCOMException) = False Or IsNothing(gobjException) = False)) Then

         Call clsError.Handle("NamedRangeExists", "clsWbk", _
              "determine if the 'workbook' named range '" & sNamedRange & "' exists.", _
              gobjCOMException, gobjException)
      End If
   End Try
End Function

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;

        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 = NamedRangeGet.Substring(1, NamedRangeGet.Length - 1);
    }
    catch (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);
    }
}

Public Shared Function Wbk_NamedRangeGet(ByVal sNamedRange As String, _
                            Optional ByVal bRemoveEquals As Boolean = True) _
                                     As String

   Dim objworkbook As Excel.Workbook

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

      Dim inamescounter As Integer

      objworkbook = gApplicationExcel.ActiveWorkbook

      For inamescounter = 1 To objworkbook.Names.Count

         If objworkbook.Names.Item(inamescounter).Name = sNamedRange Then

            NamedRangeGet = objworkbook.Names.Item(inamescounter).Value
            Exit For
         End If

      Next

      If bRemoveEquals = True Then
         NamedRangeGet = NamedRangeGet.Substring(1, NamedRangeGet.Length - 1)
      End If

   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("NamedRangeGet", "clsWbk", _
              "return the contents of the named range '" & sNamedRange & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try

End Function

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 (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'";
            if (bHidden == false)
                serrortext = "";

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

Public Shared Sub Wsh_NamedRangeConstantAdd(ByVal sWshName As String, _
                                        ByVal sNamedRange As String, _
                                        ByVal sConstantValue As String, _
                               Optional ByVal bVisible As Boolean = False, _
                               Optional ByVal bHidden As Boolean = False)

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

      Dim objworksheet As Excel.Worksheet

      objWorksheet = CType(gApplicationExcel.Worksheets(sWshName), Excel.Worksheet)

      objWorksheet.Names.Add _
         (Name:=sNamedRange, _
          RefersToR1C1:="=" & sConstantValue, _
          Visible:=bVisible)

   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 bHidden = True Then serrortext = "'Hidden'"
         If bHidden = False Then serrortext = ""

         Call clsError.Handle("NamedRangeConstantAdd", msCLASSNAME, _
              "add the " & serrortext & " named range '" & sNamedRange & "' with the constant value" & _
               "'" & sConstantValue & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Sub

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 (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);
    }
}

Public Shared Sub Wsh_NamedRangeConstantSet(ByVal sWshName As String, _
                                        ByVal sNamedRange As String, _
                                        ByVal sConstantValue As String)

   Dim objworksheet As Excel.Worksheet

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

      Dim inamescounter As Integer

      objworksheet = CType(gApplicationExcel.Worksheets(sWshName), Excel.Worksheet)

      For inamescounter = 1 To objworksheet.Names.Count
         If objworksheet.Names.Item(inamescounter).Name = sNamedRange Then
            objworksheet.Names.Item(inamescounter).RefersToR1C1 = "=" & sConstantValue
            Exit For
         End If
      Next inamescounter

   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("NamedRangeConstantSet", msCLASSNAME, _
              "change the contents of the named range '" & sNamedRange & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try

End Sub

Wsh_NamedRangeDelete

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

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

        int inamescount;
        bool bfound;

        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();
                break;
            }
        }
    }
    catch (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);
    }
}

Public Shared Function Wsh_NamedRangeDelete(ByVal sNamedRange As String, _
                                        ByVal sWshName As String, _
                               Optional ByVal sWbkName As String = "") _
                                        As Boolean

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

      Dim objWorksheet As Excel.Worksheet
      Dim objNames As Excel.Names
      Dim objName As Excel.Name

      Dim inamescount As Integer
      Dim bfound As Boolean

      bfound = False

      'need to add the same for workbooks

      objWorksheet = CType(gApplicationExcel.Worksheets(sWshName), Excel.Worksheet)

      objNames = objWorksheet.Names

      For inamescount = 1 To objNames.Count
         objName = objNames.Item(inamescount)

         If objName.Name = sWshName & "!" & sNamedRange Then
            objName.Delete()
            Exit For
         End If
      Next

   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("NamedRangeDelete", msCLASSNAME, _
              "delete the 'worksheet' named range '" & sNamedRange & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try
End Function

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);

        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 = NamedRangeGet.Substring(1, NamedRangeGet.Length - 1);
    }
    catch (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);
    }
}

Public Shared Function Wsh_NamedRangeGet(ByVal sWshName As String, _
                                     ByVal sNamedRange As String, _
                            Optional ByVal bRemoveEquals As Boolean = True) _
                                     As String

   Dim objworksheet As Excel.Worksheet

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

      Dim inamescounter As Integer

      objWorksheet = CType(gApplicationExcel.Worksheets(sWshName), Excel.Worksheet)

      For inamescounter = 1 To objWorksheet.Names.Count

         If objWorksheet.Names.Item(inamescounter).Name = sNamedRange Then

            NamedRangeGet = objWorksheet.Names.Item(inamescounter).Value
            Exit For
         End If

      Next

      If bRemoveEquals = True Then
         NamedRangeGet = NamedRangeGet.Substring(1, NamedRangeGet.Length - 1)
      End If

   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("NamedRangeGet", msCLASSNAME, _
              "return the contents of the named range '" & sNamedRange & "'.", _
              mobjCOMException, mobjException)
      End If
   End Try

End Function