Solved: Edit VBA Code using VBA?

[khalid79m]

:banghead: :eek: :mkay Hi have made a massive error,

I have over 1000 spreadsheets which I posted on our shared drive, the all have one slight error in the code, I have protected some cells that shouldn't have been

I need a code which will unlock my vba code as I have a password on it , the look in a certain module for a certian piece of code and replace it,

Can anyone help

[CreganTur]

You can't use VBA code to change VBA code. If you could it would cause some massive security issues.

You could automate this task using vTask Studio (http://www.vista-software.com/index.php). It's a great automation suite that is perfect for RAD (rapid application development). It's got an easy learning curve, so it shouldn't take you too long to build an app to do what you want. You can use it to automate anything you can do with a keyboard and mouse. Plus it has some great image matching features you can use to make sure you're interacting with the right objects (buttons, etc).

HTH:thumb

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: Forgot to mention that it has a free 30 day trial; you get to test ALL of the features- it doesn't lock you out of anything because you're using a trial.

[lucas]

khalid79m, this is why people use addins. That way everone just installs the addin and if you change the code in it.....well everyone automatically gets the change.

[jfournier]

Yeah unprotecting VBA code programmatically is very tricky and not guaranteed to work, but here are some discussions from people who have tried:

http://www.ozgrid.com/forum/showthread.php?t=13006&page=2 (http://www.pcreview.co.uk/forums/thread-989191.php)

http://www.pcreview.co.uk/forums/thread-989191.php

Once you're unprotected you can then modify the code in your project. Here's some sample code for looping through a VB project:

Sub LoopThroughVBProj()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim VBMod As VBIDE.CodeModule

Set VBProj = ThisWorkbook.VBProject
For Each VBComp In VBProj.VBComponents
Set VBMod = VBComp.CodeModule
For i = 1 To VBMod.CountOfLines
Dim CurrLine As String
CurrLine = VBMod.Lines(i, 1)
' Do whatever here...
Next i
Next VBComp
End Sub

You need to have a reference to the "Microsoft Visual Basic for Applications Extensibility" library for intellisense to work, but you could just declare VBProj, VBComp, and VBMod as objects and it should work just fine, but for getting acquainted with modifying vba code you should make the reference...

Hope that helps,

Jon

[lucas]

Hi Jon, the op says he has 1000 workbooks that he has to change. Wouldn't each of those have to have "trust access to visual basic project" checked in the security settings for your code to work?

[jfournier]

Hi Jon, the op says he has 1000 workbooks that he has to change. Wouldn't each of those have to have "trust access to visual basic project" checked in the security settings for your code to work?

To be honest I don't know if VBA programmatic access trust is stored on a workbook level, it seems on my installation of Excel 2003 that once I check it it's always checked, regardless of workbook...though that may just be to code that's signed, but I really don't know.

I wonder if it would be easier to just write some macro that will copy all those workbooks over to new workbooks that either have the right code or even better can use an addin rather than storing the code in each workbook.

[lucas]

The addin was my suggestion too.....

[khalid79m]

What is an Add in ?
How would that benefit me in the long run ????
Are there any special requirements to use add ins ?

[lucas]

An addin is a code container that can include menu's to run the code on any active sheet. It can be put on the network and everyone in the office can install it from there and use it.

How would it benifit you in the long run? Well you are now facing changing the code in 1000 workbooks. If they were running that same code from your addin all you would have to change is the code in the addin and everyone will automatically have the change.......is that enough of a benifit?

There are many advantages besides that.

addins are a built in function in Excel.

Go to the articles section and look for articles about addins. Johnske has one there I know for sure.

[lucas]

Artik
I deleted your post. Registry solutions to allow trust to visual basic project is prohibited at the forum and is considered hacking. Many know how to do this but it can't be shared here.

Please read our FAQ

[Artik]

Artik
I deleted your post.I apologize, I will never do this.
On the Polish forums (eg www.excelforum.pl (http://www.excelforum.pl), http://coderscity.net) is approached in a different way on this issue. Because security of Excel are weak, we treat them as protection against accidental changes to the file, not as a cracking of security. We do not talk about it "very loudly", but we can talk.
Other country, other habits. :)


From my deleted post remained only as much (after changes):

To be honest I don't know if VBA programmatic access trust is stored on a workbook level"Trust access to visual basic project" is stored on an application level.
For further thoughts, see the following code (in standard module):Sub aa_Test()
If Not IsAccessVBPrjct Then
MsgBox "Check ""Trust access to visual basic project""."
Else
MsgBox "Is OK"
End If
End Sub

Private Function IsAccessVBPrjct() As Boolean
Dim VBProj As Variant

On Error Resume Next
Set VBProj = ThisWorkbook.VBProject

IsAccessVBPrjct = Not IsEmpty(VBProj)

Set VBProj = Nothing
End Function
Artik

[khalid79m]

Not bad thans mate