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

Would this help my problem -- getting the AdvancedFilter method to recognize
a named range? Actually, I had trouble getting anything to work with a named
range directly. 

Susan H. 


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.

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

No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.476 / Virus Database: 269.10.2/890 - Release Date: 7/7/2007
3:26 PM