Detection of Macro Code
 

Hi,

I'm writing some code that detects if there are lines of macro code behind a
sheet in an Excel workbook. The following function returns true if it detects
code and it works fine for Excel 2000...but for some reason it doesn't seem
to work for Excel 2003. I've checked the libraries and as far as I can see
I've brought the same ones in, although two refer to Office & Excel 11.0
rather than 9.0 (if that makes a difference). For the record somewhere out
there is the individual who supplied this code to me in the first place...to
whom I'm grateful

All suggestions welcome....Rgds Chris

Private Function test_macro(Sht_name As String)

Dim totalLines As Long, beg_line As Long, test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then

For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
Next beg_line

End If

If test_line = "" Then
test_macro = False
Else
test_macro = True
End If

End Function

Detection of Macro Code
 

How does it not work?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I'm writing some code that detects if there are lines of macro code behind a
sheet in an Excel workbook. The following function returns true if it detects
code and it works fine for Excel 2000...but for some reason it doesn't seem
to work for Excel 2003. I've checked the libraries and as far as I can see
{snip}

Detection of Macro Code
 

Hi,

totalLines is 0 even when there is code in the sheet.
By the way, sht_name is a string that passes the name of the sheet in
question from another procedure to the function - it rotates through the
workbook this way.
Can you get this to work at your end in Excel 2003...??

Rgds...Chris

"Tushar Mehta" wrote:

How does it not work?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I'm writing some code that detects if there are lines of macro code behind a
sheet in an Excel workbook. The following function returns true if it detects
code and it works fine for Excel 2000...but for some reason it doesn't seem
to work for Excel 2003. I've checked the libraries and as far as I can see
{snip}

Detection of Macro Code
 

It works fine in Excel 2002.
I would alter the funtion a bit:

Private Function test_macro(Sht_name As String)

Dim totalLines As Long
Dim beg_line As Long
Dim test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then
For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
If Len(test_line) 0 And Left$(test_line, 6) < "Option" Then
test_macro = True
Exit Function
End If
Next beg_line
End If

test_macro = False

End Function


RBS


"Chris Gorham" wrote in message
...
Hi,

I'm writing some code that detects if there are lines of macro code behind
a
sheet in an Excel workbook. The following function returns true if it
detects
code and it works fine for Excel 2000...but for some reason it doesn't
seem
to work for Excel 2003. I've checked the libraries and as far as I can see
I've brought the same ones in, although two refer to Office & Excel 11.0
rather than 9.0 (if that makes a difference). For the record somewhere out
there is the individual who supplied this code to me in the first
place...to
whom I'm grateful

All suggestions welcome....Rgds Chris

Private Function test_macro(Sht_name As String)

Dim totalLines As Long, beg_line As Long, test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then

For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
Next beg_line

End If

If test_line = "" Then
test_macro = False
Else
test_macro = True
End If

End Function

Detection of Macro Code
 

Remove the 'On Error Resume Next' line and you should get better
diagnostics from the system. Using code to mask errors in general is
not a good idea since it makes it impossible to find the errors!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

totalLines is 0 even when there is code in the sheet.
By the way, sht_name is a string that passes the name of the sheet in
question from another procedure to the function - it rotates through the
workbook this way.
Can you get this to work at your end in Excel 2003...??

Rgds...Chris

"Tushar Mehta" wrote:

How does it not work?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi,

I'm writing some code that detects if there are lines of macro code behind a
sheet in an Excel workbook. The following function returns true if it detects
code and it works fine for Excel 2000...but for some reason it doesn't seem
to work for Excel 2003. I've checked the libraries and as far as I can see
{snip}

Detection of Macro Code
 

Thanks for this - however I'm still battling to get it to work in Excel 2003.
I've tried both at home and at work but with no luck, it won't detect the
number of lines.
any chance you could try this in 2003 yourself...?? Also could I ask why you
wanted to detect "option" and what the "left$" (as opposed to "left")
achieves...

Thks...Chris

"RB Smissaert" wrote:

It works fine in Excel 2002.
I would alter the funtion a bit:

Private Function test_macro(Sht_name As String)

Dim totalLines As Long
Dim beg_line As Long
Dim test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then
For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
If Len(test_line) 0 And Left$(test_line, 6) < "Option" Then
test_macro = True
Exit Function
End If
Next beg_line
End If

test_macro = False

End Function


RBS


"Chris Gorham" wrote in message
...
Hi,

I'm writing some code that detects if there are lines of macro code behind
a
sheet in an Excel workbook. The following function returns true if it
detects
code and it works fine for Excel 2000...but for some reason it doesn't
seem
to work for Excel 2003. I've checked the libraries and as far as I can see
I've brought the same ones in, although two refer to Office & Excel 11.0
rather than 9.0 (if that makes a difference). For the record somewhere out
there is the individual who supplied this code to me in the first
place...to
whom I'm grateful

All suggestions welcome....Rgds Chris

Private Function test_macro(Sht_name As String)

Dim totalLines As Long, beg_line As Long, test_line As String
Dim VBCodeMod As Object 'As CodeModule

test_line = ""

On Error Resume Next
Set VBCodeMod =
ActiveWorkbook.VBProject.VBComponents(Sheets(Sht_n ame).CodeName).CodeModule

totalLines = VBCodeMod.CountOfLines

If totalLines 0 Then

For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
Next beg_line

End If

If test_line = "" Then
test_macro = False
Else
test_macro = True
End If

End Function

Detection of Macro Code
 

This is done on XL2003 with some modification...
I do hope you can persue this matter further with the resident "Guru's"
here.
And you were right, it did not work as is on XL2003


Private Function test_macro(Sht As Worksheet) <------change


Dim totalLines As Long
Dim beg_line As Long
Dim test_line As String
Dim VBCodeMod As Object 'As CodeModule


test_line = ""


On Error Resume Next
Set VBCodeMod = _
ActiveWorkbook.VBProject.VBComponents(Sht.CodeName ).CodeModule
<---change


totalLines = VBCodeMod.CountOfLines


If totalLines 0 Then
For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
If Len(test_line) 0 And Left$(test_line, 6) < "Option"
Then
test_macro = True
Exit Function
End If
Next beg_line
End If


test_macro = False


End Function




Public Sub TstFunc()
Dim sh As Worksheet
For Each sh In Worksheets
Debug.Print test_macro(sh)
Next sh
End Sub


True
False
False

Detection of Macro Code
 

Nope,

sorry having no luck this end despite your mods...
I've wondered if this is a libraries issue. I've currently got Visual Basic
for Apps, Microsoft Excel 11.0 Object Library, OLE Automation, Microsoft
Office 11.0 Object Library and Microsoft Forms 2.0 Object Library.
What have you got selected in Excel 2003...??

Chris

"Arishy" wrote:

This is done on XL2003 with some modification...
I do hope you can persue this matter further with the resident "Guru's"
here.
And you were right, it did not work as is on XL2003


Private Function test_macro(Sht As Worksheet) <------change


Dim totalLines As Long
Dim beg_line As Long
Dim test_line As String
Dim VBCodeMod As Object 'As CodeModule


test_line = ""


On Error Resume Next
Set VBCodeMod = _
ActiveWorkbook.VBProject.VBComponents(Sht.CodeName ).CodeModule
<---change


totalLines = VBCodeMod.CountOfLines


If totalLines 0 Then
For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
If Len(test_line) 0 And Left$(test_line, 6) < "Option"
Then
test_macro = True
Exit Function
End If
Next beg_line
End If


test_macro = False


End Function




Public Sub TstFunc()
Dim sh As Worksheet
For Each sh In Worksheets
Debug.Print test_macro(sh)
Next sh
End Sub


True
False
False

Detection of Macro Code
 

Not sure the libraries can be the problem as it uses late binding.
What happens if you follow Tushar Mehta's suggestion and take out
On Error Resume Next?

RBS

"Chris Gorham" wrote in message
...
Nope,

sorry having no luck this end despite your mods...
I've wondered if this is a libraries issue. I've currently got Visual
Basic
for Apps, Microsoft Excel 11.0 Object Library, OLE Automation, Microsoft
Office 11.0 Object Library and Microsoft Forms 2.0 Object Library.
What have you got selected in Excel 2003...??

Chris

"Arishy" wrote:

This is done on XL2003 with some modification...
I do hope you can persue this matter further with the resident "Guru's"
here.
And you were right, it did not work as is on XL2003


Private Function test_macro(Sht As Worksheet) <------change


Dim totalLines As Long
Dim beg_line As Long
Dim test_line As String
Dim VBCodeMod As Object 'As CodeModule


test_line = ""


On Error Resume Next
Set VBCodeMod = _
ActiveWorkbook.VBProject.VBComponents(Sht.CodeName ).CodeModule
<---change


totalLines = VBCodeMod.CountOfLines


If totalLines 0 Then
For beg_line = 1 To totalLines
test_line = Trim(VBCodeMod.Lines(beg_line, 1))
If Len(test_line) 0 And Left$(test_line, 6) < "Option"
Then
test_macro = True
Exit Function
End If
Next beg_line
End If


test_macro = False


End Function




Public Sub TstFunc()
Dim sh As Worksheet
For Each sh In Worksheets
Debug.Print test_macro(sh)
Next sh
End Sub


True
False
False