C# Snippets

Wbk_NamedRangeConstantAdd

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 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_NamedRangeDelete

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