Finding commas in code

[Fire_Chief]

I have MANY MANY dim statements in my code and I wrote them like below

Dim a, x, c, f

As I learn code I realize I should write them more like this :

Dim a as string
Dim x as integer ….. and so on.

Is there a vba code that will find the commas in the code, put it on the next line and change it to Dim so?

Dim a, x, c, f will look like:

Dim a
Dim x
Dim c
Dim f

This would make it SO much easier for me to find what kind of variable it is and change it.

 

[jpvh8]

One could write self-modifying code to do that, after registering the VBA Extensibility library and relaxing the Trust Center settings to Trust Access to the project object model. I do not recommend that, though, because you can easily break your project and that trust setting increases your risk from malware. If you learn something about regex, you could use that with a text editor, e.g., Notepad++, on exported code modules or within the VBA editor if you install MZ-Tools.

 

[shg]

To just find such lines, you could tick Search Project and Use Pattern Matching in the Find dialog, and search for Dim *,

 

[Fire_Chief]

Thank You both for the reply. Searching out the variables has been hard because I use the same word in other variables. IE: Column, First_Column and Second_Column etc... The FIND function does not give me a way to distinguish between them. I think I will just have to do it manually.

Thanks Again

 

[Rick Rothstein]

Thank You both for the reply. Searching out the variables has been hard because I use the same word in other variables. IE: Column, First_Column and Second_Column etc... The FIND function does not give me a way to distinguish between them.

Put a check mark in the "Find Whole Word Only" checkbox on the Find dialog box maybe?

 

[Fire_Chief]

I tried that but it cannot distinguish between column and first_column etc. I have many similar variables with the word column in it.

Thanks

 

[Rick Rothstein]

I tried that but it cannot distinguish between column and first_column etc. I have many similar variables with the word column in it.

Hmm, you are absolutely right and I learned something new... the VB editor Replace dialog does not see underline characters as being part of a word. The reason I never noticed this before is that I do not use underlined characters in my variable, function or subroutine names; rather, I prefer to use a form of "camel notation" where I lower case all letters except for the first letter of words or abbreviated words within the variable name. So rather than writing first_column, I would use FirstColumn instead. Using that type of notation, as it turns out, eliminates the problem you are now facing. As to your problem, I think you will need to do what jpvh8 suggested at the end of Message #2 .

 

[Fire_Chief]

Actually I am going to go with your idea and try to change mind to the way you did it.
Thanks