CODEWITHSUNDEEP
arraysexcelvbadynamic
ectrimm
ectrimm

Reputation: 35

Extracting the sheet name from an array

I have defined an array with two variables: the name of the sheet and the pagesetup.printarea. 

varArray(i) = ThisWorkbook.Sheets(i).Name & " " & Worksheets(i).PageSetup.PrintArea

The problem is that the sheetname includes a number, which I do not need (e.g., China2). How can I extract just the string part of the sheetname and the printarea separately? Or, is there an better way to set this up to begin with? Thanks.

Upvotes: 1

Views: 408

Answers (2)

brettdj
brettdj

Reputation: 55682

Just expand your curent approach and use a 2D array.

Which IMO is better suited that a dictionary in that you can expand the number of elements if required and as the critical reason to use a dictionary (multiple occurences) is absent.

Also uses a regexp to clean, but takes out all digits.

Sub Test()
Dim x() As String
Dim ws As Worksheet
Dim lngCnt As Long

ReDim x(1 To ActiveWorkbook.Sheets.Count, 1 To 2)

For Each ws In ActiveWorkbook.Sheets
    lngCnt = lngCnt + 1
    x(lngCnt, 1) = CleanStr(ws.Name)
    x(lngCnt, 2) = ws.PageSetup.PrintArea
Next

End Sub

optional cleaning function

Function CleanStr(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
   .Pattern = "\d+"
   .Global = True
   CleanStr = .Replace(strIn, vbNullString)
End With
End Function

Upvotes: 0

Maciej Los
Maciej Los

Reputation: 8591

As i mentioned in comment, there's a better way to store sheet names and print areas. It's called Dictionary. For example:

'needs reference to Microsoft Scripting Runtime
Dim dic As Dictionary

Set dic = New Dictionary
dic.Add "SheetName", "PrintArea"


'get print area for sheet
Dim sPrintArea As String
sPrintArea = dic("SheetName")

As you can see, now you can access to the print area via sheet name ;)

If you want to enumerate all keys and values, use for...each loop:

Dim oKey As Variant
For Each oKey In dic.Keys
 MsgBox oKey & vbTab & dic(oKey)
Next

If you want to get only China, you may use Regex within Excel:

'needs reference to Microsoft VBScript Regular Expressions x.x
Dim oRegex As VBScript_RegExp_55.RegExp

Set oRegex = New VBScript_RegExp_55.RegExp

sPattern = "\d{1,}"
With oRegex
    .Pattern = sPattern
    .MultiLine = False
End With
sPureSheetName = oRegex.Replace(sSheetName, "")

Upvotes: 3

Related Questions