PDA

View Full Version : Find all hard coded references



theta
09-27-2012, 06:08 AM
Hi all...back for some more sterling advice :)

I have a project that opens another 20 workbooks (all with references to a main workbook). So under the VBE I have 20 separate projects.

Within these project, there is heavy usage of hard coded ranges that I would now like to replace with named ranges e.g.

$A1$Z54
B7:B12
$F$7:$F$12


When I say heavy usage - I mean hundreds! Spread across 23,000+ lines of code.

Is there a way to build a macro that will go through every line of code and do a LIKE operation or a REGEX operation so that I can capture all of these hard coded references into an array? If I get to this stage I am sure I can then take it further to dump the results into a worksheet :


REF....BOOK.........MODULE.....LINE....VALUE
1......Book1.xls....VB_Main....56......Sheet1!$A7:$Z15
2......Book1.xls....VB_Main....59......Sheet2!$B$7:$Z14
3......Book2.xls....VB_Func....5.......wks.Range("A7")

mikerickson
09-27-2012, 07:22 AM
How would you distinquish between code like


Target.EntrieRow.Range("D1")
and

Sheet1.Range("D1")


You could use the .Find method of the .CodeModule object to find all the lines that conain the string ".Cells" or the string ".Range".

theta
09-27-2012, 07:43 AM
That's why i'm here to chat to the experts :)

I was thinking it would be a REGEX to analyse items between " " that match the pattern :

[T][N]...............A1........Single cell
[T][N] : [T][N]......A1:A10....Range
[T] : [T]............A:A.......Column
[N] : [N]............1:1.......Row

[T] = Text
[N] = Number

Anything else will be a named range, which is what I am trying to implement across all of the code

theta
09-27-2012, 07:47 AM
Rough pseudo, check every line of code for every workbook in VBE :

Does line contain " * "
--->Does the text within the " " match any of the REGEX patterns
------>If TRUE copy the text between " " and dump it on Book1.xls along with
------>workbook, module, line information
Move on to next line and repeat

:)

snb
09-27-2012, 09:09 AM
Waht should be the benefit of using named ranges over hardcoded ranges ?
Named ranges are as hardcoded as any hardcoded range.
If you prefer dynamic ranges you better use VBA's facilities: currentregion, areas, usedrange, specialcells(2), etc.
The UserInterface lacks all those facilities, so a surrogate of named ranges was necessary.
In my view a named range is a functionality you need in the userinterface; as soon as you turn to VBA it has no advantage.

theta
09-27-2012, 09:30 AM
I have several different workbooks that are loaded by this project.

Users can hack and mash them up as much as they want, but there is a dynamic named range that catches the "data" element needed by VBA.

I am now going through changing all hard coded references to refer to the dynamic named ranges I have not set up in these workbooks.

Bob Phillips
09-27-2012, 09:39 AM
Named ranges are as hardcoded as any hardcoded range.

Nonsense!

If you have a named range rng pointing at say A1:M10, then to start

Range("rng")

and

Range("A1:M10")

are equivalent.

Insert a row at row 5 say, and

Range("rng").Address is now A1:M11

whereas

Range("A1:M10").Address is still A1:M10

theta
09-27-2012, 10:24 AM
So any ideas on this? I can't even find any coding exampes to analyse every line of code within every VBE project.

If I could do that, the REGEXP shouldn't be too hard :/ (he says)

shrivallabha
09-27-2012, 11:11 AM
Maybe: Export all modules. And then analyze exported files as if they were text files.

You can use following for this:
http://www.appspro.com/Utilities/CodeCleaner.htm

snb
09-28-2012, 12:56 AM
To retrieve all VBA code in all codemodules:


Sub snb()
For Each pr In Application.VBE.VBProjects
For Each vc In pr.vbcomponents
c01 = c01 & vbCr & vc.codemodule.Lines(1, vc.codemodule.countoflines)
Next
Next
End Sub

theta
09-28-2012, 02:58 AM
Thanks snb. I have built a function now and am testing it. Will upload when tested fully - for some advice on making it efficient :)

AirCooledNut
09-28-2012, 12:39 PM
Waht should be the benefit of using named ranges over hardcoded ranges ?...
In my view a named range is a functionality you need in the userinterface; as soon as you turn to VBA it has no advantage.
I totally disagree. If but for one reason: Readability!

This is probably a preference thing but I find named ranges far more readable, especially at a future date (when the project is over) OR if someone else is looking at it.

list_UserNames is more understandable than $B32:$D50 inside a formula. This includes VB code.

Just one coders opinion :whistle:

AirCooledNut
09-28-2012, 12:44 PM
Maybe: Export all modules. And then analyze exported files as if they were text files.

You can use following for this:
http://www.appspro.com/Utilities/CodeCleaner.htm

MZ-Tools (http://www.mztools.com/v3/download.aspx) would be better. I use both but for a Find/Replace MZ-Tools is it.

AirCooledNut
09-28-2012, 12:50 PM
Won't you need to change all ranges in the cell formulas to the named range as well or are the ranges ONLY used in the VB Project?

theta
10-01-2012, 03:02 AM
I have been through all worksheets and created dynamic named ranges. Trying to get to the point where the VBA is "dumb" and just handles the inputs fed to it.

Do not want it to run like a 'black box' where there is no visibility from the spreadsheet. All variables and ranges are stored in the sheet and fed to the VBA to process.

Hopefully... :)