Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Array question for Excel VBA

Posted on 2002-06-14
7
Medium Priority
?
500 Views
Last Modified: 2012-06-21
I need to build an array that only should contain unique strings that must not exist more the once in the array. (even if the range can include duplicates of the string)

I have a range A1:A100 to conatins strings (names to other sheets in a Excel workbook).

I need to filll an array with all the strings in this range, but there must NOT be any duplicates of the strings. And I also need to check if the string in each cell of the range is a name to a worksheet that exist.

Does anyone know how to do this in VBA?
I am a delphi coder, and how not found any good VBA reference.


Example:

Lets say I have a range in Excel that looks like this:

    A        B        C        D
1  Sheet1
2  Sheet2
3  Sheet1
4  Sheet4
5  Sheet3
6  Sheet2
7  MySheet
8  TestSheet
9  Sheet2


Lets say that my Excel Workbook has a coresponding sheet for each of this strings, except for "TestSheet" in cell A8. I now need to build an array that looks like:

 Array ("Sheet1","Sheet2","Sheet4","Sheet3","MySheet")

A unique string should only exist once in the array and if there is no worksheet with the same name as the string, it should also not be included in the array.

Can all this be done with VBA in Excel?


Best Regards
Magnus Flysjö

0
Comment
Question by:Probie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7077560
Check out the following menu of Excel:

Data->Filter->Advanced Filter
*Choose the range you want to filter (ie A1:A100),
*choose the destination (eventually same range...)
*choose unique records only
click OK

CHeers
0
 
LVL 1

Author Comment

by:Probie
ID: 7077659
is it possible to put the result into an array instead of in cells?

Range("A1:A100").AdvancedFilter Action:=xlFilterCopy, Range:="B1:B100", Unique:=True

Like this (into MyArray):

Range("A1:A100").AdvancedFilter Action:=xlFilterCopy, Range:=MyArray, Unique:=True
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7077690
No, this isn't possible. you have first to Filter to a Range (maybe you can use a temporary workbook only for that), and then build your array from the results.
CHeers
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:Julian_K
ID: 7078013
You'll have some example code in 5 minutes.
0
 
LVL 5

Accepted Solution

by:
Julian_K earned 800 total points
ID: 7078048
Option Explicit

Public Sub Test()
        Dim Result As Variant, I As Long
    Result = TestNames(ActiveWindow.RangeSelection)
   
    For I = 1 To UBound(Result)
        Debug.Print Result(I)
    Next I
   
    Erase Result
End Sub

Public Function TestNames(ByVal SheetNameRange As Range) As Variant
        'Declare some Variables for operating the cycles.
        'There are better decisions, but this is just an example
        Dim I As Long, L As Long, J As Long, K As Long
        Dim strSheetName As String
       
        'Return Value: an array of string
        Dim retArr() As String
       
    'Initialize the array
    ReDim Preserve retArr(0)
   
    'Get how many rows are there in the range being sent.
    L = SheetNameRange.Rows.Count
   
    'For each Row in the Range
    '(This cycle can be substituted with for each... next)
    For I = 1 To L
       
        'Get the cell content (expected to be a sheet name)
        strSheetName = SheetNameRange.Cells(I, 1)
       
        'Check if this sheet Exists
        If SheetExists(strSheetName) Then
       
            'If exists, then verify if it has been added to result array, or not
            K = UBound(retArr)
            For J = 1 To K
                If retArr(J) = strSheetName Then Exit For
            Next J
           
            'If the cycle has finished, that means this name has not been found.
            'Then add it to the result array
            If J > K Then
                ReDim Preserve retArr(J)
                retArr(J) = strSheetName
            End If
           
        End If
   
        'next cell
    Next I
   
    'Return the result
    TestNames = retArr
   
    'Clear the memory
    Erase retArr
   
End Function


Private Function SheetExists(ByVal strSheetName As String) As Boolean
    On Error Resume Next
    SheetExists = False
    SheetExists = Not (ThisWorkbook.Sheets.Item(CStr(strSheetName)) Is Nothing)
    On Error GoTo 0
End Function
0
 
LVL 5

Expert Comment

by:Julian_K
ID: 7078051
I write it in a hurry, there may be some bugs :-(
But I have to go home and had no time for more...
0
 
LVL 1

Author Comment

by:Probie
ID: 7080224
Great Job...

It worked perfect...




0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question