How to get the index numbers of certain members of a collection in order to use them later?

[Larry]

I have a macro that inadvertently uninstalls all the installed Add-ins.
Nothing can be done about that. So I want to create code that, before
the macro runs, gets the index number of each of the currently installed
add-ins, and then, after the macro runs, uses those index numbers to
re-install the add-ins that have been uninstalled.

Let's say there are four add-ins in the Add-ins collection, and the
first and third Addin are currently installed. I run something like
this:

For Each myAddin In Application.AddIns
If myAddin.Installed = True Then
myAddin.Installed = False
X = myAddin.index
End If
Next myAddin

But of course after this code is run, X will only be equal to 3, the
index of the last addin that was installed. What code do I put in that
place that would reserve the index number of all of the add-ins that are
being uninstalled? And then how would I access those numbers to
re-install the add-ins later?

I've only used very simple arrays in the past so I'm not expert in
arrays at all. Though maybe an array would not be necessary.

Thanks for any help.
Larry

 

[Helmut Weber]

Hi Larry,

most of the following is untested,
as I don't dare to install a dozen addins.

You can count the addins: AddIns.Count

You can dimension an array:
Dim ArrAdd() As String
ReDim ArrAdd(AddIns.Count)

You can store the addins fullname there:

For l = 1 To AddIns.Count
ArrAdd(l) = AddIns(1).Path & "\" & AddIns(l).Name
Next

Note that without "option base 1" there is an unused
entry (0) in the array. which doesn't do any harm.

You still have to store the names somewhere,
if the addins are _deleted_, not just uninstalled.
An uninstalled addin is still in the addins collection.


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"




--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Helmut Weber]

Another typo,

just in case you get puzzled:

ArrAdd(l) = AddIns(1).Path & "\" & AddIns(l).Name

! ArrAdd(l) = AddIns(l).Path & "\" & AddIns(l).Name
^

To be viewed with an non-proportional font.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Larry]

For l = 1 To AddIns.Count
ArrAdd(l) = AddIns(1).Path & "\" & AddIns(l).Name
Next

But this would be reserving the whole collection. I only want to
reserve the add-ins that are currently installed. However, maybe that
will work anyway. Let's continue.

I don't need the whole path, only the name is needed to re-install an
uninstalled add-in which is still in the collection.

So, let's say I do this with four add-ins, two of which are currently
installed:

Dim ArrAdd() As String
ReDim ArrAdd(AddIns.Count)

For i = 1 To AddIns.Count
If AddIns(i).Installed = True Then
ArrAdd(i) = AddIns(i).Name
AddIns(i).Installed = False
End If
Next

At this point, all the add-ins have been uninstalled. Now the question
is, how do I re-install only the add-ins that were uninstalled by the
macro? (I don't want to install the ones that were not installed to
begin with.)

 

[Helmut Weber]

Hi Larry

hmm, if I get you right,
you want a list of all installed addins.

Like this and in many other was:

Sub Test500()
Dim oAdd As AddIn
Dim ArrAdd() As String
Dim l As Long
l = 0
For Each oAdd In AddIns
If oAdd.Installed = True Then
l = l + 1
ReDim Preserve ArrAdd(l)
ArrAdd(l) = oAdd.Name
End If
Next
' call another macro to uninstall some or all addins
' restore the uninstalled addins
For l = 1 To UBound(ArrAdd)
If AddIns(ArrAdd(l)).Installed = False Then
AddIns(ArrAdd(l)).Installed = True
End If
MsgBox ArrAdd(l) ' for testing
Next

End sub

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Larry]

Thank you very much Helmut. I've got it working. I just had to make
two changes.

The first change was cosmetic, so that I could read it better. I
changed your "I", which in my code window is virtually identical to the
number "1", to a little "i" so I could read it.

Then I ran into an error with this line:

If AddIns(ArrAdd(i)).Installed = False Then

And I understand why (ArrAdd(i)) is a string, not an index number.

So to re-install the installed add-ins, I used this code instead :

' restore the uninstalled addins
For i = 1 To UBound(ArrAdd)
' I'm adding this
For Each oAdd In AddIns
If oAdd.Name = ArrAdd(i) Then
oAdd.Installed = True
End If
Next
MsgBox ArrAdd(i) ' for testing
Next

Now the whole test macro looks like this:

Dim oAdd As AddIn
Dim ArrAdd() As String
Dim i As Long
i = 0
For Each oAdd In AddIns
If oAdd.Installed = True Then
i = i + 1
ReDim Preserve ArrAdd(i)
ArrAdd(i) = oAdd.Name
' I'm adding this line to uninstall the installed add-in
oAdd.Installed = False
End If
Next
' I added this:
MsgBox "Number of uninstalled add-ins in array " & UBound(ArrAdd)

' restore the uninstalled addins
For i = 1 To UBound(ArrAdd)
' I'm adding this
For Each oAdd In AddIns
If oAdd.Name = ArrAdd(i) Then
oAdd.Installed = True
End If
Next
MsgBox ArrAdd(i) ' for testing
Next

Larry

 

[Larry]

Helmut,

There's something about this code I don't understand, the meaning of
ArrAdd(i).

Look at these two lines.

ReDim Preserve ArrAdd(i)
ArrAdd(i) = oAdd.Name

The first line is changing the number of elements in the array, as
represented by ArrAdd(i). The second line is setting the current array
element, represented by ArrAdd(i), as the name of the current add-in.
So ArrAdd(i) is both the array, AND a particular element of the array.
Is that the correct way of understanding it?

Larry

 

[Jay Freedman]

Helmut,

There's something about this code I don't understand, the meaning of
ArrAdd(i).

Look at these two lines.

ReDim Preserve ArrAdd(i)
ArrAdd(i) = oAdd.Name

The first line is changing the number of elements in the array, as
represented by ArrAdd(i). The second line is setting the current array
element, represented by ArrAdd(i), as the name of the current add-in.
So ArrAdd(i) is both the array, AND a particular element of the array.
Is that the correct way of understanding it?

Larry

Hi Larry,

Not quite...

The ReDim statement changes the size of the array. ArrAdd is the name
of the array to change, and the (i) says what the new size should be.

In the assignment statement, the (i) selects a particular element from
the array.

Despite the similar appearance of ArrAdd(i) in both places, the uses
are different. I suppose when the ReDim statement was being created,
they could have made the syntax something else, like

ReDim ArrAdd() To i

but they didn't. It's just one of those things you need to get used
to.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Larry]

Thanks, Jay.

Hi Larry,

Not quite...

The ReDim statement changes the size of the array. ArrAdd is the name
of the array to change, and the (i) says what the new size should be.

In the assignment statement, the (i) selects a particular element from
the array.

Despite the similar appearance of ArrAdd(i) in both places, the uses
are different. I suppose when the ReDim statement was being created,
they could have made the syntax something else, like

ReDim ArrAdd() To i

but they didn't. It's just one of those things you need to get used
to.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Larry]

This will be kindergarten stuff to most people here, but arrays are new
to me and I'm fascinated by them, the way such simple code can be so
powerful. Here's a practice array I just wrote which types a paragraph
below the current paragraph in which the word order of the first
paragraph is reversed.


Application.ScreenUpdating = False
Dim myString() As String
Dim i As Long
i = 1
For i = 1 To Selection.Paragraphs(1).Range.Words.Count
Selection.Paragraphs(1).Range.Words(i).Select
ReDim Preserve myString(i)
myString(i) = Selection.Text
Next

Selection.Paragraphs(1).Range.Collapse wdCollapseEnd
Selection.TypeParagraph

For i = UBound(myString) To 1 Step -1
Selection.TypeText myString(i)
Next

 

[Larry]

Or, even better, this macro reverses not just the word order but the
character order, though it takes a while to run.


Application.ScreenUpdating = False
Dim r As Range
Set r = Selection.Range

Dim myString() As String
Dim i As Long
i = 1
For i = 1 To Selection.Paragraphs(1).Range.Characters.Count
Selection.Paragraphs(1).Range.Characters(i).Select
ReDim Preserve myString(i)
myString(i) = Selection.Text
Next

Selection.Paragraphs(1).Range.Collapse wdCollapseEnd
Selection.TypeParagraph

For i = UBound(myString) To 1 Step -1
Selection.TypeText myString(i)
Next
r.Select

 

[Greg Maxey]

Larry,

Not to criticize (almost everything I know somebody showed me), but you can
speed that up using ranges:

Sub ArrayTest()
Dim myString() As String
Dim myRng As Word.Range
Dim chrRng As Word.Range
Dim i As Long
Dim t As Single
t = Timer
Application.ScreenUpdating = False
Set myRng = Selection.Range
While myRng.Start <> myRng.End
Set chrRng = myRng.Characters(1)
ReDim Preserve myString(i)
myString(i) = chrRng.Text
i = i + 1
myRng.Start = myRng.Start + 1
Wend
myRng.InsertAfter vbCr
For i = UBound(myString) To 0 Step -1
myRng.InsertAfter myString(i)
Next
MsgBox Timer - t
End Sub

 

[Larry]

Greg,

Yes, that's faster, thanks. What is the purpose of the timer?

Larry

 

[Greg Maxey]

So you can measure the time it takes to run the code.

 

[Larry]

Oh I get it. Sorry I was thick. When you declare t = Timer at the start
of the macro, t equals the number of seconds from midnight to the
instant the macro starts, so that Timer - t at the end of the macro gets
the elapsed time of the macro.

And the difference between using Range and using Selection is
significant, but it's not an order of magnitude, which was what I
expected. Operating on a short paragraph, my macro using Selection
takes about .66 second. Your macro using Range takes about .22 second.
I look forward to using the Timer on other macros.

Larry

 

[Larry]

Here's another useful task an array can perform. Let's say I'm working
on a group of several documents, and I want to close them for a while to
do other things, but be able re-open the same group later without
searching them out.

First I have a declared public array variable at the top of the module:

Public ArrDocs() As String

Then when I want to close a bunch of documents to be re-opened later I
run
this:

' create array of docs

msg = "Close and save changes in open documents " & vbCr _
& "and put them in array for re-opening later."
If MsgBox(msg, vbOKCancel, "Close Docs for Re-Opening as Group") = vbOK
Then

Dim myDoc As Document
Dim i As Long
i = 0
For Each myDoc In Documents
If myDoc.Path <> "" Then
i = i + 1
ReDim Preserve ArrDocs(i)
ArrDocs(i) = myDoc.FullName
myDoc.Close wdSaveChanges
End If
Next
'MsgBox i
End If

Then when I want to re-open that group of documents, I run this:

' open docs that were stored in array

msg = "Re-open documents that were placed in array earlier."
If MsgBox(msg, vbOKCancel, "Re-Open Document Group") = vbOK Then

Dim myDoc As Document
For i = 1 To UBound(ArrDocs)
Documents.Open (ArrDocs(i))
Next
End If

But let's say I've closed Word in the meantime, so that the publicly
declared array is empty. I'm not sure it's possible, but maybe I could
use the PrivateProfileString method to run document paths stored in
C:\Settings.Txt. I've used that approach for re-opening individual
documents. Maybe it's possible, with an array, to do the same with a
group of documents as well.

Larry

 

[Helmut Weber]

Hi Larry,

I've never used "PrivateProfileString",
beause I didn't need it.

Loop though your array,
store the documents' names in a file,
and read the file later, like that:

Sub StoreThem()
Dim l As Long
Open "c:\preserve.txt" For Output As #1
For l = 1 To 20
Print #1, Format(l, "0000")
Next
Close #1
End Sub
' ----------
Sub ReadThem()
Dim s As String
Open "c:\preserve.txt" For Input As #1
While Not EOF(1)
Input #1, s
ActiveDocument.Range.InsertAfter s & vbCr
Wend
Close #1
End Sub

HTH

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Greg Maxey]

Helmut,

Then with your method there is really no need for an array:

Option Explicit
Sub CloseFilesAndStoreList()
Dim myFile As String
Dim myDoc As Document
Open "e:\Junk\MyFileList.txt" For Output As #1
For Each myDoc In Documents
Print #1, myDoc.FullName
myDoc.Close wdSaveChanges
Next
Close #1
End Sub

Sub OpenListOfFiles()
Dim myFile As String
Open "e:\Junk\MyFileList.txt" For Input As #1
While Not EOF(1)
Input #1, myFile
Documents.Open (myFile)
Wend
Close #1
End Sub

As Anne Troy likes to say, "Cool Beans."

 

[Helmut Weber]

Hi Submariner,

very much so.

Hi Larry,

if you got hooked on arrays,
then learn about sorting by a sorting master like Howard Kaikow.
http://www.standards.com/Sorting/SortPerformanceComparison-Description.html

Try to remove double entries from an array,
and close the so created gaps.
Try to count the number of occurances of equal items.
Compare two arrays and get the number of different items...

Make the arrays very large,
to get used to timing and memory problems.

And many very entertaining things more.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Greg Maxey]

Larry,

I liked your idea of opening a group of files with the click of a button and
added the technique suggested by Helmut to my website.

http://gregmaxey.mvps.org/Open_Group_of_Files.htm