|
Detect macros in an Excel file
I am using macros in one file to audit another file. The second file should
not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Some information on dealing w/the VBProject on Chip's site:
http://www.cpearson.com/excel/vbe.htm#ListAllInProject I've never had the need to deal w/the VBProject programmatically, but maybe this will give you a place to start. "Gleam" wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
http://www.google.com/groups?hl=en&l...4a6e1da&rnum=1
You could try a couple of the suggestions in this link. HTH Paul ------------------------------------------------------------------- I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
You can use this to test it
If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Added in xl2007?
-- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Thanks Tom
Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
It is useful in 2007, I use it in my mail code examples and on this SaveAs page
http://www.rondebruin.nl/saveas.htm It compile in older versions so the macro can be used in all versions -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Thanks Tom Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
I am not sure what you mean by "It compile in older versions " as the command
Debug.Print ActiveWorkbook.hasvbproject does not work in 2003. It is good to know that Microsoft have improved things as this would be a good portable solution. "Ron de Bruin" wrote: It is useful in 2007, I use it in my mail code examples and on this SaveAs page http://www.rondebruin.nl/saveas.htm It compile in older versions so the macro can be used in all versions -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Thanks Tom Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Hi Gleam
The macro examples on my site compile OK See How I test the application version in the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am not sure what you mean by "It compile in older versions " as the command Debug.Print ActiveWorkbook.hasvbproject does not work in 2003. It is good to know that Microsoft have improved things as this would be a good portable solution. "Ron de Bruin" wrote: It is useful in 2007, I use it in my mail code examples and on this SaveAs page http://www.rondebruin.nl/saveas.htm It compile in older versions so the macro can be used in all versions -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Thanks Tom Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Gleam,
If you put that command in a module in xl2003 and then did Debug = Compile VBAProject it does not raise an error. The error only occurs if the code attempts to execute the commend. I believe Ron is saying he has designed his code so that command isn't executed except in xl2007. So obviously it doesn't provide any functionality (you can't use it to check for code) in versions earlier than 2007. -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi Gleam The macro examples on my site compile OK See How I test the application version in the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am not sure what you mean by "It compile in older versions " as the command Debug.Print ActiveWorkbook.hasvbproject does not work in 2003. It is good to know that Microsoft have improved things as this would be a good portable solution. "Ron de Bruin" wrote: It is useful in 2007, I use it in my mail code examples and on this SaveAs page http://www.rondebruin.nl/saveas.htm It compile in older versions so the macro can be used in all versions -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Thanks Tom Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Correct
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Gleam, If you put that command in a module in xl2003 and then did Debug = Compile VBAProject it does not raise an error. The error only occurs if the code attempts to execute the commend. I believe Ron is saying he has designed his code so that command isn't executed except in xl2007. So obviously it doesn't provide any functionality (you can't use it to check for code) in versions earlier than 2007. -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi Gleam The macro examples on my site compile OK See How I test the application version in the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am not sure what you mean by "It compile in older versions " as the command Debug.Print ActiveWorkbook.hasvbproject does not work in 2003. It is good to know that Microsoft have improved things as this would be a good portable solution. "Ron de Bruin" wrote: It is useful in 2007, I use it in my mail code examples and on this SaveAs page http://www.rondebruin.nl/saveas.htm It compile in older versions so the macro can be used in all versions -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Thanks Tom Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Many thanks for the clarification.
"Ron de Bruin" wrote: Correct -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Gleam, If you put that command in a module in xl2003 and then did Debug = Compile VBAProject it does not raise an error. The error only occurs if the code attempts to execute the commend. I believe Ron is saying he has designed his code so that command isn't executed except in xl2007. So obviously it doesn't provide any functionality (you can't use it to check for code) in versions earlier than 2007. -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... Hi Gleam The macro examples on my site compile OK See How I test the application version in the code -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am not sure what you mean by "It compile in older versions " as the command Debug.Print ActiveWorkbook.hasvbproject does not work in 2003. It is good to know that Microsoft have improved things as this would be a good portable solution. "Ron de Bruin" wrote: It is useful in 2007, I use it in my mail code examples and on this SaveAs page http://www.rondebruin.nl/saveas.htm It compile in older versions so the macro can be used in all versions -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Thanks Tom Added in xl2007? Yes Workbook.HasVBProject Property Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Read-only Boolean. Version Added: Excel 2007 I get used to 2007<g Is that good or bad ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Tom Ogilvy" wrote in message ... Added in xl2007? -- Regards, Tom Ogilvy "Ron de Bruin" wrote in message ... You can use this to test it If ActiveWorkbook.HasVBProject then ............... -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Gleam" wrote in message ... I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Define a function
Function wkbkHasMacros(fName$) As Boolean ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Dim m As VBComponent ' if Run-time error '1004' ' Programmatic access to Visual Basic Project is not trusted. ' then Excel Tools - Macro - Security, ' Click to enable "Trust access to Visual Basic Project" wkbkHasMacros = False For Each m In Workbooks(fName$).VBProject.VBComponents ' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object If m.Type = 1 Then wkbkHasMacros = True Exit Function End If Next m End Function and call it with wkbkHasMacros(ActiveWorkbook.Name) Carl. On Mar 16, 4:26 pm, Gleam wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
Carl
I get a compile error at this line: Dim m As VBComponent "user-defined type not defined." Do I need to activate a reference library? Some help on this would be appreciated as I have just searced help with "reference libraries" and found nothing helpful "Carl Hartness" wrote: Define a function Function wkbkHasMacros(fName$) As Boolean ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Dim m As VBComponent ' if Run-time error '1004' ' Programmatic access to Visual Basic Project is not trusted. ' then Excel Tools - Macro - Security, ' Click to enable "Trust access to Visual Basic Project" wkbkHasMacros = False For Each m In Workbooks(fName$).VBProject.VBComponents ' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object If m.Type = 1 Then wkbkHasMacros = True Exit Function End If Next m End Function and call it with wkbkHasMacros(ActiveWorkbook.Name) Carl. On Mar 16, 4:26 pm, Gleam wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? |
Detect macros in an Excel file
' if Compile error:
' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Gleam wrote: Carl I get a compile error at this line: Dim m As VBComponent "user-defined type not defined." Do I need to activate a reference library? Some help on this would be appreciated as I have just searced help with "reference libraries" and found nothing helpful "Carl Hartness" wrote: Define a function Function wkbkHasMacros(fName$) As Boolean ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Dim m As VBComponent ' if Run-time error '1004' ' Programmatic access to Visual Basic Project is not trusted. ' then Excel Tools - Macro - Security, ' Click to enable "Trust access to Visual Basic Project" wkbkHasMacros = False For Each m In Workbooks(fName$).VBProject.VBComponents ' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object If m.Type = 1 Then wkbkHasMacros = True Exit Function End If Next m End Function and call it with wkbkHasMacros(ActiveWorkbook.Name) Carl. On Mar 16, 4:26 pm, Gleam wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? -- Dave Peterson |
Detect macros in an Excel file
Thank you for bringing this to my attention - less haste more speed on my part!
Still not a portable solution until "everyone" is on Excel 2007 "Dave Peterson" wrote: ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Gleam wrote: Carl I get a compile error at this line: Dim m As VBComponent "user-defined type not defined." Do I need to activate a reference library? Some help on this would be appreciated as I have just searced help with "reference libraries" and found nothing helpful "Carl Hartness" wrote: Define a function Function wkbkHasMacros(fName$) As Boolean ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Dim m As VBComponent ' if Run-time error '1004' ' Programmatic access to Visual Basic Project is not trusted. ' then Excel Tools - Macro - Security, ' Click to enable "Trust access to Visual Basic Project" wkbkHasMacros = False For Each m In Workbooks(fName$).VBProject.VBComponents ' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object If m.Type = 1 Then wkbkHasMacros = True Exit Function End If Next m End Function and call it with wkbkHasMacros(ActiveWorkbook.Name) Carl. On Mar 16, 4:26 pm, Gleam wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? -- Dave Peterson |
Detect macros in an Excel file
Try changing one line (and remove the reference):
Dim m As Object Gleam wrote: Thank you for bringing this to my attention - less haste more speed on my part! Still not a portable solution until "everyone" is on Excel 2007 "Dave Peterson" wrote: ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Gleam wrote: Carl I get a compile error at this line: Dim m As VBComponent "user-defined type not defined." Do I need to activate a reference library? Some help on this would be appreciated as I have just searced help with "reference libraries" and found nothing helpful "Carl Hartness" wrote: Define a function Function wkbkHasMacros(fName$) As Boolean ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Dim m As VBComponent ' if Run-time error '1004' ' Programmatic access to Visual Basic Project is not trusted. ' then Excel Tools - Macro - Security, ' Click to enable "Trust access to Visual Basic Project" wkbkHasMacros = False For Each m In Workbooks(fName$).VBProject.VBComponents ' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object If m.Type = 1 Then wkbkHasMacros = True Exit Function End If Next m End Function and call it with wkbkHasMacros(ActiveWorkbook.Name) Carl. On Mar 16, 4:26 pm, Gleam wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? -- Dave Peterson -- Dave Peterson |
Detect macros in an Excel file
Many thanks
"Dave Peterson" wrote: Try changing one line (and remove the reference): Dim m As Object Gleam wrote: Thank you for bringing this to my attention - less haste more speed on my part! Still not a portable solution until "everyone" is on Excel 2007 "Dave Peterson" wrote: ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Gleam wrote: Carl I get a compile error at this line: Dim m As VBComponent "user-defined type not defined." Do I need to activate a reference library? Some help on this would be appreciated as I have just searced help with "reference libraries" and found nothing helpful "Carl Hartness" wrote: Define a function Function wkbkHasMacros(fName$) As Boolean ' if Compile error: ' User-defined type not defined ' then VBE Tools - Reference, ' check Microsoft Visual Basic for Applications Extensibility 5.3 Dim m As VBComponent ' if Run-time error '1004' ' Programmatic access to Visual Basic Project is not trusted. ' then Excel Tools - Macro - Security, ' Click to enable "Trust access to Visual Basic Project" wkbkHasMacros = False For Each m In Workbooks(fName$).VBProject.VBComponents ' m.Type, 1=module, 2=Class, 3=Form, 100=Excel object If m.Type = 1 Then wkbkHasMacros = True Exit Function End If Next m End Function and call it with wkbkHasMacros(ActiveWorkbook.Name) Carl. On Mar 16, 4:26 pm, Gleam wrote: I am using macros in one file to audit another file. The second file should not have any macros. Is there a way I can detect this? -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 05:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com