From a dynamically built list of strings, how do I create a variant containing an array? I am looping through my code and listing selected sheets in my workbook.
	Is it possible to create a variant containing an array similar to the variants that built-in functions return?
	How do I find the last occurrence of a string within another string?
	How do I make case-insensitive string comparisons?
	How do I find the name assigned to a cell?
	How can I make my code run faster?

From a dynamically built list of strings, how do I create a variant containing an array? I am looping through my code and listing selected sheets in my workbook.
Keywords: Dynamic String Variant Array
Posted September 11, 1996

Your question contains two parts, so two methods are discussed below. A general method of creating an array from a dynamically built list is given below. Notice that two ways of building the string list are shown inside the For loop. One of them is commented out because it is less readable but both ways work. 

Dim vArray As Variant
Dim szList As String
Dim oSheet As Object
   For Each oSheet In ThisWorkbook.Sheets
      ''' Next line uses two double-quotes to insert a quote.
      'szList = szList & ",""" & oSheet.Name & """"
      ''' Next line uses the Chr$() function to insert a quote
      szList = szList & "," & Chr$(34) & oSheet.Name & Chr$(34)
   Next o
   szList = "{" & Mid$(szList, 2) & "}"
   vArray = Application.Evaluate(szList)
			

For your specific case dealing with sheets in a workbook, an alternative method is possible because it is possible to know the maximum number of sheets beforehand.  

Dim vArray As Variant
Dim iCount As Integer
   vArray = Array()
   ReDim vArray(1 To ThisWorkbook.Sheets.Count)
   With ThisWorkbook.Sheets
      For iCount = 1 To.Count
         vArray(iCount) = .Item(iCount).Name
      Next iCount
   End With
			

Is it possible to create a variant containing an array similar to the variants that built-in functions return?
Keywords: Variant Array Base Option Subscript Index
Posted September 11,1996

Yes. Use the Array function to create variants containing arrays. Example: 

v = Array(4, 3, 4, 2, 0, -5, 5)
v = Array("Jan", "Feb", "Mar")
			

Remember to insert the 

Option Base 1
			

statement at the top of your module to be able to subscript the array as v(1), v(2) and so on. If you do not insert this statement, Excel creates zero-based arrays by default so you will have to access the array elements as v(0), v(1) and so on.

How do I find the last occurrence of a string within another string?
Keywords: String Occurrence
Posted July 6, 1996

You can write a function that uses the built-in InStr() function. You are in luck! Here's a readymade version we wrote recently.

''' --------------------------------------------------------------------
''' Function:   InStrLast(iStart As Integer, szSrchIn As String, _
'''                szSrchFor As String, iCompare As Integer) As Integer
'''
''' Comments:   Returns the last occurrence of one string within
'''             another. Starts at specified location and handles
'''             case-sensitive searches as required.
'''             Name and parameters mirror the built-in InStr().
'''
''' Arguments:  iStart is the starting position for the search
'''             szSrchIn is the string being searched
'''             szSrchFor is the string being sought
'''             iCompare specifies the comparison method as follows:
'''                 0 (zero) means case-sensitive search
'''                 1 means case-insensitive
'''
''' Returns:    The position of the last occurrence of sSrchFor in
'''             sSrchIn, 0 (zero) if not found.
'''
''' Keywords:   LAST SUBSTRING SEARCH
'''
'''             (c) 1996 Baarns Consulting Group, Inc.
'''
''' Date        Developer                               Action
''' --------------------------------------------------------------------
''' 05/08/96    Ganesh Ram                              Created
'
Function InStrLast(iStart As Integer, szSrchIn As String, _
                    szSrchFor As String, iCompare As Integer) As Integer
Dim iPrevFoundAt As Integer
Dim iFoundAt As Integer
    On Error GoTo ErrExit_InStrLast
    iPrevFoundAt = 0
    iFoundAt = InStr(iStart, szSrchIn, szSrchFor, iCompare)
    Do While iFoundAt > 0
        iPrevFoundAt = iFoundAt
        iFoundAt = InStr(iPrevFoundAt + 1, szSrchIn, szSrchFor, iCompare)
    Loop
ErrExit_InStrLast:
    If Err <> 0 Then MsgBox Error$, vbExclamation
    InStrLast = iPrevFoundAt
    Exit Function
End Function
			

How do I make case-insensitive string comparisons?
Keywords: String Comparison Case-sensitive StrComp
Posted May 15, 1996

Three approaches are available as shown in the example code below. The advantages and disadvantages of these approaches are also discussed. 

  ''' Approach 1 of 3
If UCase$(szName1) = UCase$(szName2) Then
   ''' Approach 2 of 3
Option Compare Text     ''' Module-level statement
If szName1 = szName2 Then
   ''' Approach 3 of 3
If StrComp(szName1, szName2, 1) = 0 Then
			

Approach 1 of 3: Convert strings to uppercase (or lowercase) and compare

This method is commonly used in other programming languages. It has the advantage of making it explicit that a case-insensitive comparison is being performed. Its disadvantage is that it requires two function calls which is not desirable in large loops.

Approach 2 of 3: Use StrComp() function

This built-in function compares two strings and returns -1, 0, or 1. Here, we are interested in the zero return value which indicates that the strings are equal. The third parameter to this function is optional. If omitted, the Option Compare setting for the module is used. If the number 1 is passed, StrComp performs a case-insensitive comparison, if zero is passed, it performs a case-sensitive comparison.

Approach 3 of 3: Specify Option Compare setting for the module

To make all string comparisons within a module case-insensitive (textual comparison), place an 

Option Compare Text
			

statement at the beginning of your module.

Disadvantage: When needed, you must remember to use StrComp to perform case-sensitive string comparisons.

How do I find the name assigned to a cell?
Keywords: Define Name Range
Posted April 22, 1996

To determine the name defined for a cell range, you can use the Names method of the Workbook object as described below. The Names method is often used to find the cell range or formula assigned to a particular name. But it can also be used to do the reverse operation. Here's how. 

Dim oName As Object
Dim szReference As String
      ''' Using the Names method to find the range referred to by a name
   MsgBox ActiveWorkbook.Names("MyName").RefersTo
      ''' Using the Names method to find if a cell has a name
   On Error Resume Next
   szReference = "=" & ActiveSheet.Name & "!" & ActiveCell.Address
   Set oName = ActiveWorkbook.Names(RefersTo:=szReference)
   If Err = 0 Then
      MsgBox "Active cell is named '" & oName.Name & "'." ''' Displays name if defined
   Else
      MsgBox "No name defined for active cell."
   End If
			

How can I make my code run faster?
Keywords: Speed Optimize
Posted January 16, 1996

The most common problem with slow running code is the referencing of multiple properties on the same object using the full object specification each time. An example of this would be:

Sub Bad ()
    ActiveSheet.Range("A1").Font.Bold = True
    ActiveSheet.Range("A1").Font.Italic = True
    ActiveSheet.Range("A1").Font.ColorIndex = 6
    ActiveSheet.Range("A1").Font.Size = 24
End Sub
	

In the above code there are four object accesses on each line. Each object access takes a finite amount of time. This is somewhat notable in-process and incredibly slow cross-process. A good rule of thumb is to minimize the number of dots which represent object accesses.

The With statement is used to reduce the number of object accesses. It allows you to perform a series of statements on a specified object without requalifying the name of the object.

The following code is much more efficient:

Sub Good ()
    With ActiveSheet.Range("A1").Font
        .Bold = True
        .Italic = True
        .ColorIndex = 6
        .Size = 24
    End With
End Sub
	

Back to Top

Back to the FAQ Table of Contents

You have the right to remain silent. Anything you say will be misquoted, then used against you.