Array in VBA

[JNW]

I work with a program that stores excel files using encrypted names. The
program then assigns the file a recognizable name that I am able to retrieve
through VBA.

I am creating a tool to allow users to arrange a given number of workbooks
through an add-in that has a userform.

The problem: I can get everything to work if I have the encrypted file name
show up in the userform, but the user can't tell what file it is, because the
name makes no sense.

How can I display the recognizable name I retrieve through VBA but still
have the encrypted name used to resize the window.

P.S. I can get this to work if I drop all the names in an excel worksheet
and do a vlookup. I'm trying to avoid using a sheet to do the work.

Here is the userform code.
Private Sub Button_Horiz_Click()
'Horizontally arranges selected workbooks
Dim ictr As Long
Dim wkbkCtr As Long
SomeWkbkWasSelected = False
wkbkCtr = -1
With Me.ListBox1
ReDim WkbkNames(0 To .ListCount - 1)
For ictr = 0 To .ListCount - 1
If .Selected(ictr) = True Then
SomeWkbkWasSelected = True
wkbkCtr = wkbkCtr + 1
WkbkNames(wkbkCtr) = .List(ictr)
End If
Next ictr
End With
ReDim Preserve WkbkNames(0 To wkbkCtr)
ArrangeHorizontally
Unload Me
End Sub

Private Sub Button_Vert_Click()
'Vertically arranges selected workbooks
Dim ictr As Long
Dim wkbkCtr As Long
SomeWkbkWasSelected = False
wkbkCtr = -1
With Me.ListBox1
ReDim WkbkNames(0 To .ListCount - 1)
For ictr = 0 To .ListCount - 1
If .Selected(ictr) = True Then
SomeWkbkWasSelected = True
wkbkCtr = wkbkCtr + 1
WkbkNames(wkbkCtr) = .List(ictr)
End If
Next ictr
End With
ReDim Preserve WkbkNames(0 To wkbkCtr)
ArrangeVertically
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim wkbk As Workbook
Dim wkbknm As String
Dim myWin As Window

Me.ListBox1.MultiSelect = fmMultiSelectMulti
For Each wkbk In Application.Workbooks
For Each myWin In wkbk.Windows
If myWin.Visible = True Then
If Left(wkbk.Name, 1) = "{" Then
With wkbk.ActiveSheet.Range("A65536")
.Formula = "=wpname()"
wkbknm = .Value
.ClearContents
End With
Me.ListBox1.AddItem wkbknm
Exit For
Else
Me.ListBox1.AddItem wkbk.Name
Exit For
End If
End If
Next myWin
Next wkbk
End Sub

Here is the module code
Option Explicit
Public WkbkNames() As String
Public SomeWkbkWasSelected As Boolean

Sub ArrangeVertically()
Dim ictr As Long
Dim a As Long 'chosen books
Dim h As Long 'height
Dim l As Long 'left
Dim w As Long 'width
Dim wkbknm As String

ActiveWindow.WindowState = xlMaximized
a = 0
h = ActiveWindow.Height - 25
h = h
l = 0
w = ActiveWindow.Width
h = h
If SomeWkbkWasSelected = True Then
For ictr = LBound(WkbkNames) To UBound(WkbkNames)
'Debug.Print WkbkNames(ictr) & ictr + 1
a = ictr + 1
Next ictr
w = w / a
For ictr = LBound(WkbkNames) To UBound(WkbkNames)
'''''error happens right here. need to find a way to activate the
next workbook
'''''the problem is when the nextworkbook is an epace file and the
name has been
'''''encrypted
'''''1. find a way to activate the file, OR
'''''2. find a way to pull the real name without activating, OR
'''''3. create a name for the array differently

Workbooks(WkbkNames(ictr)).Activate
If ActiveWorkbook.Name <> Workbooks(WkbkNames(ictr)).Name Then
With ActiveSheet.Range("A65536")
.Formula = "=wpname"
wkbknm = .Value
.ClearContents
End With
If wkbknm <> Workbooks(WkbkNames(ictr)).Name Then
GoTo OnToNext
End If
End If
'Workbooks(WkbkNames(ictr)).Activate
With ActiveWindow
.WindowState = xlNormal
.Top = 0
.Left = l
.Width = w
.Height = h
l = .Left + .Width
End With
OnToNext:
Next ictr
End If
End Sub


Sub ArrangeHorizontally()
Dim ictr As Long
Dim a As Long 'chosen books
Dim h As Long 'height
Dim t As Long 'left
Dim w As Long 'width

ActiveWindow.WindowState = xlMaximized
a = 0
h = ActiveWindow.Height - 20
h = h
t = 0
w = ActiveWindow.Width - 5
w = w
If SomeWkbkWasSelected = True Then
For ictr = LBound(WkbkNames) To UBound(WkbkNames)
'Debug.Print WkbkNames(ictr) & ictr + 1
a = ictr + 1
Next ictr
h = h / a
For ictr = LBound(WkbkNames) To UBound(WkbkNames)
Workbooks(WkbkNames(ictr)).Activate
With ActiveWindow
.WindowState = xlNormal
.Left = 0
.Top = t
.Width = w
.Height = h
t = .Top + .Height
End With
Next ictr
End If
End Sub

 

[Tom Ogilvy]

The obvious solution is to translate the encrypted name into the unencrypted
equivalent.

try this demo:

Sub Demo1()
Dim v(1 To 4, 1 To 2)
v(1, 1) = "A"
v(1, 2) = "B"
v(2, 1) = "C"
v(2, 2) = "D"
v(3, 1) = "E"
v(3, 2) = "F"
v(4, 1) = "G"
v(4, 2) = "H"
res = Application.VLookup("G", v, 2, False)
If Not IsError(res) Then
MsgBox "Name G translates to: " & res
Else
MsgBox "Name G not found"
End If
End Sub

so you can do this with a two dimensional array.

 

[JNW]

I want to make sure I understand. In this example B, D, F, and H are the
encrypted names. The rest are the unencrypted equivalent. And I can set
A,B, etc. either by my own string, or, say, v(1,2) = workbook(2).name.

Am I way off on this?

I've always been fuzzy on 2D arrays. Thanks for this succint explaination.