Mysterious VBA code in Excel (searching for something)

[Arrabonae]

Dear All--

I am thinking about this code a couple days now. I cannot help but wondering what on Earth did it written. I can understand that this code looking for some kind of macro named

[LEFT][COLOR=#333333]Worksheet_SelectionChange.[/COLOR][/LEFT]

But Why?


Also the part of the code:

[COLOR=#333333] .ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)"[/COLOR]

is blurry as wall as

[COLOR=#333333] StartLine = .CountOfDeclarationLines + 1" [/COLOR]

and

[COLOR=#333333] Do Until StartLine >= .CountOfLines 
[/COLOR][COLOR=#333333]msg = .ProcOfLine(StartLine, vbext_pk_Proc)"[/COLOR]

Can anyone help me? This code written in Excel 2003 and does not run under 2010. I am not sure It is even useful routine.... Thank you very much!


PS: Oh, yea... this is French stuff!

[COLOR=#323333][FONT=Courier]Function ChercheSaisieEncadree(Ipurge As Boolean) As Boolean[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]ChercheSaisieEncadree = False[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]Dim nomProc As Variant[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]nomProc = "Worksheet_SelectionChange"[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]'fs, mpfe (d'aprčs Chip Pearson)[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]'nécessite une référence ŕ Microsoft Visual Basic Extensibility[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]Dim VBCodeMod As CodeModule[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]Dim StartLine As Long[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]Dim msg As String, Ret$, done As Boolean[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]Dim ProcName As String[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]' With ActiveWorkbook.VBProject.VBComponents("Feuil2").CodeModule[/FONT][/COLOR]

[COLOR=#323333][FONT=Courier]'  For I = 1 To ActiveWorkbook.VBProject.VBComponents.Count[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    Set VBCodeMod = _[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]      ThisWorkbook.VBProject.VBComponents("Feuil2").CodeModule[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    With VBCodeMod[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]      StartLine = .CountOfDeclarationLines + 1[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]      Do Until StartLine >= .CountOfLines[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        msg = .ProcOfLine(StartLine, vbext_pk_Proc)[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        If msg = nomProc Then[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]         ' Ret = "La procédure '" & nomProc & "' est dans le module '"[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]         ' Ret = Ret & .Parent.Name & "' de ce classeur."[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]          Ret = "start line " & StartLine & vbLf[/FONT][/COLOR]

[COLOR=#323333][FONT=Courier]  Ret = Ret & "nB lIGNES " & .ProcCountLines(.ProcOfLine(StartLine, _[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                    vbext_pk_Proc), vbext_pk_Proc) & vbLf[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    Dim i As Long[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    For i = StartLine To StartLine + .ProcCountLines(.ProcOfLine(StartLine, _[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                    vbext_pk_Proc), vbext_pk_Proc)[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        Ret = Ret & .Lines(i, 1) & vbCr[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    Next[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]         ' MsgBox Ret[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]         done = True[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]         If Ipurge Then 'purge eventuelle[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]     .DeleteLines StartLine, .ProcCountLines(.ProcOfLine(StartLine, vbext_pk_Proc), vbext_pk_Proc)[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]         End If[/FONT][/COLOR]

[COLOR=#323333][FONT=Courier]          Exit Do[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        End If[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]                    vbext_pk_Proc), vbext_pk_Proc)[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]      Loop[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    End With[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]'  Next[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier] ' If Not done Then _[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    MsgBox "La procédure '" & nomProc & "' n'existe pas dans ce classeur"[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]
[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]      ChercheSaisieEncadree = done[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]
[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier] 'MsgBox done[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]
[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]End Function[/FONT][/COLOR]

 

[Iktishaf]

The general idea of the function is to inspect the VBA code pages behind a workbook to look for a specifically named subroutine, which it then informs the user about and optionally deletes it.

The name

Worksheet_SelectionChange ​

refers to procedures that are activated by the Selection-Change Event, i.e. whenever you select a cell, or range of cells, on a worksheet. Obviously, you can change this name, but usually only procedures controlling events are going to have the same name on every worksheet code page.

The procedure then examines all the code pages behind the worksheets (this part has been commented out and replaced only by looking at Sheet2 (Feuil2 in French).
It then looks at each line on the VBA code page, searching for a match with the name Worksheet.SelectionChange. It then knows it has found the start of this subroutine. It therefore prints out a message (also commented out)to tell the user on which line it has found the subroutine along with the sheet name on which it was found (the parent). It then calculates where the procedure ends and optionally deletes it.

Its use? I suppose you may forget where you have written certain procedures and want to track them down. Or you may want to investigate other people's writing. Perhaps other contributors can suggest other uses.

 

[Arrabonae]

The general idea of the function is to inspect the VBA code pages behind a workbook to look for a specifically named subroutine, which it then informs the user about and optionally deletes it.

The name

Worksheet_SelectionChange ​

refers to procedures that are activated by the Selection-Change Event, i.e. whenever you select a cell, or range of cells, on a worksheet. Obviously, you can change this name, but usually only procedures controlling events are going to have the same name on every worksheet code page.

The procedure then examines all the code pages behind the worksheets (this part has been commented out and replaced only by looking at Sheet2 (Feuil2 in French).
It then looks at each line on the VBA code page, searching for a match with the name Worksheet.SelectionChange. It then knows it has found the start of this subroutine. It therefore prints out a message (also commented out)to tell the user on which line it has found the subroutine along with the sheet name on which it was found (the parent). It then calculates where the procedure ends and optionally deletes it.

Its use? I suppose you may forget where you have written certain procedures and want to track them down. Or you may want to investigate other people's writing. Perhaps other contributors can suggest other uses.

Thanks for your help!
I have got an error with this code in Excel 2010. It is written in 2003 but I got runtime error '1004' on this line:
We tried with Thisworkbook or Parent or any other cases.

Dim VBCodeMod As CodeModule
  Set VBCodeMod = _
      ActiveWorkbook.VBProject.VBComponents("Feuil2").CodeModule

Can you help me why?

 

[Iktishaf]

One major difference in Excel 2010 over earlier versions is that MS tried to improve security. You code will run (provided you change Feuil2 to the name of an actual worksheet in your workbook) by following these steps.
1. Select the File menu
2. Select Options
3. Select Trust Center
4. Click the Trust Center Settings button
5. Select Macro Settings
6. Click the checkbox "Trust access to the VBA project object model."

 

[Arrabonae]

One major difference in Excel 2010 over earlier versions is that MS tried to improve security. You code will run (provided you change Feuil2 to the name of an actual worksheet in your workbook) by following these steps.
1. Select the File menu
2. Select Options
3. Select Trust Center
4. Click the Trust Center Settings button
5. Select Macro Settings
6. Click the checkbox "Trust access to the VBA project object model."

Thank you, thank you very much!
It is really a helpful tip!

This option is effective on the Workbook or on my office? I mean if I will open this workbook in other PC, it will run smoothly or first I need to set up the other PC's office as well?
So whenever I try to run this code on a new PC I should make this settings first?

 

[Arrabonae]

One major difference in Excel 2010 over earlier versions is that MS tried to improve security. You code will run (provided you change Feuil2 to the name of an actual worksheet in your workbook) by following these steps.
1. Select the File menu
2. Select Options
3. Select Trust Center
4. Click the Trust Center Settings button
5. Select Macro Settings
6. Click the checkbox "Trust access to the VBA project object model."

Thank you, thank you very much!
It is really a helpful tip!

AS I understand this option will effect the Office not the workbook, so I will try to write a code for Automatically Check this option every new office where this file is opened.

thanks Again!

 

[Iktishaf]

I'm glad I was able to help.