[dba-Tech] Using Defined Names in VBA (was RE: Error handling inExcel)

I use this function to determine if a range exists.
PS I normally run this function from Access hence appXcel which should
be deleted if you are running from within Excel.
HTH 
Jim Hale

 Private Function RangeNameExists(nname) As Boolean
'   Returns TRUE if the range name exists
    Dim n As Excel.Name
    RangeNameExists = False
    For Each n In appXcel.ActiveWorkbook.Names
        If UCase(n.Name) = UCase(nname) Then
            RangeNameExists = True
            Exit Function
        End If
    Next n
    Set n = Nothing
End Function

-----Original Message-----
From: dba-tech-bounces at databaseadvisors.com
[mailto:dba-tech-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Sunday, July 08, 2007 3:33 PM
To: 'Discussion of Hardware and Software issues'
Subject: [dba-Tech] Using Defined Names in VBA (was RE: Error handling
inExcel)

Well, the following works, but seems rather convoluted to me. OrderID is
a
defined range. UniqueList is a single cell where the AdvancedFilter
feature
creates a unique list from the items in OrderID. I didn't expect using a
defined name to be so difficult -- and it probably isn't. There is
probably
an easier way to accomplish this. If anyone has suggestions, I'm
listening.
I do absolutely no development in Excel -- I'm winging it. 

Susan H. 


Function UniqueList()
  'Populate control with
  'unique list.
  Dim rgSource As Range
  Dim rgTarget As Range
  'Get source and unique list
  'range addresses.
  Range("OrderID").Select
  Set rgSource = Selection
  Range("UniqueList").Select
  Set rgTarget = Selection
  'Create unique list.
  Range(rgSource.Address).AdvancedFilter Action:=xlFilterCopy, _
   CopyToRange:=Range(rgTarget.Address), Unique:=True
  'Set combo control's Row Source property.
  rgTarget.Activate
  UserForm1.cboUniqueList.RowSource = Selection.CurrentRegion.Address
  'Display user form.
  UserForm1.Show
End Function 


Way over my head I'm afraid, sorry Susan


Jon


_______________________________________________
dba-Tech mailing list
dba-Tech at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-tech
Website: http://www.databaseadvisors.com

***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.