Find all hard coded references

[theta]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

[AirCooledNut]

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 would be better. I use both but for a Find/Replace MZ-Tools is it.

[AirCooledNut]

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]

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...