Using Excel XP.
How would I get a list of all the variables, their type (public, private,
including arrays, no need for local variables), the module and the subs or
functions they are used in in a worksheet?
Had a look at different freeware utilities, but found nil yet that can do
this.
The purpose is to make it easier to split up one large module into smaller
ones.
Thanks for any advice.

RBS

Thanks for the tips. I have done the splitting up of a too large module the
hard way; that is by trial and error.
I might have a go later to make a Word macro that does what I want. It still
could be useful to get a list of the variables as mentioned for example to
organise the variables in such a way that you use the smallest possible
scope. I am sure at the moment I have public variables where they could be
private by moving them to the right module.
If somebody already has code (in Excel, Word or whatever) that could make
such a list I still would be interested.

RBS

"Bob Kilmer" wrote in message
...
I just wrote a VB6 addin in a day or so that will (among other things)
list
all procedures by module and project in a project group using objects and
techniques similar to the ones explained he
http://www.cpearson.com/excel/vbe.htm . You could do the same for Excel.
The
code need not be in an addin, but could be in a regular project.

If you don't want to write your own utility, you might copy a modules
worth
of code at a time into a text editor that can sort the lines then sort the
lines. Those that begin with Public, Private, Dim, Const, Type, Enum, Sub,
Function will be variables, constants or procedures. If you are concerned
with their order in the original module, number the lines (include leading
zeros), sort by the column following the numbers, delete lines you don't
want (or copy out those you do), then resort by the first column. You may
discover other tricks. UltraEdit is an editor that will let you do the
foregoing. You can download a free, fully functional, time limited copy at
www.ultraedit.com or download a registered copy for - last I looked - $35.
UltraEdit will list functions in a file it thinks is a VB file
(*.bas,*.cls,*.frm,*.ctl) in a list that can be copied. It might not
identify all procedures and may identify some variables that are not
procedures out of the box, but I was able easily modify the "WORDFILE.TXT"
that it uses to ID them so that it will.

If you don't need local variables, then the variables you do want will be
at
the top of the modules.

--
Bob Kilmer


"RB Smissaert" wrote in message
...
Using Excel XP.
How would I get a list of all the variables, their type (public,
private,
including arrays, no need for local variables), the module and the subs
or
functions they are used in in a worksheet?
Had a look at different freeware utilities, but found nil yet that can
do
this.
The purpose is to make it easier to split up one large module into
smaller
ones.
Thanks for any advice.

RBS

Thanks to the examples on Chip Pearson's website I could make a quite useful
sub that list the different VBProject components.
In case somebody might find this useful, this is the code:

Function CompTypeToName(VBComp As VBComponent) As String

'taken from: http://www.cpearson.com/excel/vbe.htm
'-------------------------------------------------

Select Case VBComp.Type
Case vbext_ct_ActiveXDesigner
CompTypeToName = "ActiveX Designer"
Case vbext_ct_ClassModule
CompTypeToName = "Class Module"
Case vbext_ct_Document
CompTypeToName = "Document"
Case vbext_ct_MSForm
CompTypeToName = "MS Form"
Case vbext_ct_StdModule
CompTypeToName = "Standard Module"
Case Else
End Select

End Function


Sub ListModules()

Dim i As Byte
Dim VBProj As VBProject
Dim VBComp As VBComponent
Dim compCount As Integer
Dim wb As Workbook
Dim ai As AddIn
Dim compArray()

For Each wb In Application.Workbooks
Select Case MsgBox("LIST THE VB COMPONENTS OF THIS WORKBOOK?"
& _
vbCrLf & vbCrLf & _
wb.Name, _
vbYesNoCancel + vbQuestion + vbDefaultButton1, _
"LIST VB COMPONENTS")

Case vbYes
Set VBProj = wb.VBProject
Exit For
Case vbCancel
Exit Sub
End Select
Next

If VBProj Is Nothing Then
For Each ai In Application.AddIns
If ai.Installed = True Then
Select Case MsgBox("LIST THE VB COMPONENTS OF THIS
ADD-IN?" & _
vbCrLf & vbCrLf & _
ai.Name, _
vbYesNoCancel + vbQuestion +
vbDefaultButton2, _
"LIST VB COMPONENTS")

Case vbYes
Set VBProj = Application.Workbooks(ai.Name).VBProject
Exit For
Case vbCancel
Exit Sub
End Select
End If
Next
End If

If VBProj Is Nothing Then
Exit Sub
End If

compCount = VBProj.VBComponents.Count

ReDim compArray(1 To compCount + 1, 1 To 4)

compArray(1, 1) = "Component Type"
compArray(1, 2) = "Component Name"
compArray(1, 3) = "Count Of Lines"
compArray(1, 4) = "Count Of Declaration Lines"

i = 1

On Error Resume Next 'for in case you can't do .CountOfLines
For Each VBComp In VBProj.VBComponents
i = i + 1
compArray(i, 1) = CompTypeToName(VBComp)
compArray(i, 2) = VBComp.Name
compArray(i, 3) = VBComp.CodeModule.CountOfLines
compArray(i, 4) = VBComp.CodeModule.CountOfDeclarationLines
Next

Application.ScreenUpdating = False

Cells.Clear

Range(Cells(1), Cells(i, 4)) = compArray

'sort by component type then by CountOfLines both ascending
Range(Cells(1), Cells(i, 4)).Sort Key1:=Cells(1), _
Order1:=xlAscending, _
Key2:=Cells(3), _
Order2:=xlAscending, _
Header:=xlYes

With Range(Cells(1), Cells(4))
.Font.Bold = True
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
End With

Range(Cells(1), Cells(i, 4)).Columns.AutoFit

Application.ScreenUpdating = True

End Sub


RBS


"Bob Kilmer" wrote in message
...
I just wrote a VB6 addin in a day or so that will (among other things)
list
all procedures by module and project in a project group using objects and
techniques similar to the ones explained he
http://www.cpearson.com/excel/vbe.htm . You could do the same for Excel.
The
code need not be in an addin, but could be in a regular project.

If you don't want to write your own utility, you might copy a modules
worth
of code at a time into a text editor that can sort the lines then sort the
lines. Those that begin with Public, Private, Dim, Const, Type, Enum, Sub,
Function will be variables, constants or procedures. If you are concerned
with their order in the original module, number the lines (include leading
zeros), sort by the column following the numbers, delete lines you don't
want (or copy out those you do), then resort by the first column. You may
discover other tricks. UltraEdit is an editor that will let you do the
foregoing. You can download a free, fully functional, time limited copy at
www.ultraedit.com or download a registered copy for - last I looked - $35.
UltraEdit will list functions in a file it thinks is a VB file
(*.bas,*.cls,*.frm,*.ctl) in a list that can be copied. It might not
identify all procedures and may identify some variables that are not
procedures out of the box, but I was able easily modify the "WORDFILE.TXT"
that it uses to ID them so that it will.

If you don't need local variables, then the variables you do want will be
at
the top of the modules.

--
Bob Kilmer


"RB Smissaert" wrote in message
...
Using Excel XP.
How would I get a list of all the variables, their type (public,
private,
including arrays, no need for local variables), the module and the subs
or
functions they are used in in a worksheet?
Had a look at different freeware utilities, but found nil yet that can
do
this.
The purpose is to make it easier to split up one large module into
smaller
ones.
Thanks for any advice.

RBS

Take a look at http://www.mztools.com/ This is not the answer to all of
your prayers, but it has some handy utilities. The Find functionality shows
you where every instance of a search target is within the scope (procedure,
module, project) that you sepcify. Handy for quickly learning where
variables are used in a project. Double-click to go there. Also includes
some analysis tools.

--
Bob Kilmer


"Bob Kilmer" wrote in message
...
I just wrote a VB6 addin in a day or so that will (among other things)
list
all procedures by module and project in a project group using objects and
techniques similar to the ones explained he
http://www.cpearson.com/excel/vbe.htm . You could do the same for Excel.
The
code need not be in an addin, but could be in a regular project.

If you don't want to write your own utility, you might copy a modules
worth
of code at a time into a text editor that can sort the lines then sort the
lines. Those that begin with Public, Private, Dim, Const, Type, Enum, Sub,
Function will be variables, constants or procedures. If you are concerned
with their order in the original module, number the lines (include leading
zeros), sort by the column following the numbers, delete lines you don't
want (or copy out those you do), then resort by the first column. You may
discover other tricks. UltraEdit is an editor that will let you do the
foregoing. You can download a free, fully functional, time limited copy at
www.ultraedit.com or download a registered copy for - last I looked - $35.
UltraEdit will list functions in a file it thinks is a VB file
(*.bas,*.cls,*.frm,*.ctl) in a list that can be copied. It might not
identify all procedures and may identify some variables that are not
procedures out of the box, but I was able easily modify the "WORDFILE.TXT"
that it uses to ID them so that it will.

If you don't need local variables, then the variables you do want will be
at
the top of the modules.

--
Bob Kilmer


"RB Smissaert" wrote in message
...
Using Excel XP.
How would I get a list of all the variables, their type (public,
private,
including arrays, no need for local variables), the module and the subs
or
functions they are used in in a worksheet?
Had a look at different freeware utilities, but found nil yet that can
do
this.
The purpose is to make it easier to split up one large module into
smaller
ones.
Thanks for any advice.

RBS

Thanks, but got that one already.

RBS

"Bob Kilmer" wrote in message
...
Take a look at http://www.mztools.com/ This is not the answer to all of
your prayers, but it has some handy utilities. The Find functionality
shows
you where every instance of a search target is within the scope
(procedure,
module, project) that you sepcify. Handy for quickly learning where
variables are used in a project. Double-click to go there. Also includes
some analysis tools.

--
Bob Kilmer


"Bob Kilmer" wrote in message
...
I just wrote a VB6 addin in a day or so that will (among other things)
list
all procedures by module and project in a project group using objects
and
techniques similar to the ones explained he
http://www.cpearson.com/excel/vbe.htm . You could do the same for Excel.
The
code need not be in an addin, but could be in a regular project.

If you don't want to write your own utility, you might copy a modules
worth
of code at a time into a text editor that can sort the lines then sort
the
lines. Those that begin with Public, Private, Dim, Const, Type, Enum,
Sub,
Function will be variables, constants or procedures. If you are
concerned
with their order in the original module, number the lines (include
leading
zeros), sort by the column following the numbers, delete lines you don't
want (or copy out those you do), then resort by the first column. You
may
discover other tricks. UltraEdit is an editor that will let you do the
foregoing. You can download a free, fully functional, time limited copy
at
www.ultraedit.com or download a registered copy for - last I looked -
$35.
UltraEdit will list functions in a file it thinks is a VB file
(*.bas,*.cls,*.frm,*.ctl) in a list that can be copied. It might not
identify all procedures and may identify some variables that are not
procedures out of the box, but I was able easily modify the
"WORDFILE.TXT"
that it uses to ID them so that it will.

If you don't need local variables, then the variables you do want will
be
at
the top of the modules.

--
Bob Kilmer


"RB Smissaert" wrote in message
...
Using Excel XP.
How would I get a list of all the variables, their type (public,
private,
including arrays, no need for local variables), the module and the
subs
or
functions they are used in in a worksheet?
Had a look at different freeware utilities, but found nil yet that can
do
this.
The purpose is to make it easier to split up one large module into
smaller
ones.
Thanks for any advice.

RBS