Amend VBA by using different VBA

[xlStar]

Picture this.

I have created a workbook with macro for other department (they are on different Network) in different building.

Sometimes I have to amend the macro, so thus means I kept having to resave the file in different name all the time (i.e Workbook v2.xls etc) and email or walk over to them and re-intall the new version workbook onto their shared drive.

I was wondering if I could write a macro on different workbook that will enable to amend the macro on another workbook (where I could insert the correct filepath on cell A1). So I could email it to them and they just simply click on one button. (they are not Excel confident).

Is that possible? I am a self-taught macro writer and doesn't know the full advance of visual basic.

 

[Yesuslave]

Sounds to me like you need a simple .exe file. However, since VBA doesn't produce these, I'm at a loss. If you have the regular VB developer's environment, then you should be able to produce a simple executable that could do this.

Sorry I couldn't be more help,
Joshua Wise

 

[Nelviticus]

By default, Office blocks VBA code from being able to modify other VBA code. This is for security reasons. Depending on the version of Office you're using, you can disable this security by going into Tools/Options, selecting the Security tab, clicking the Macro Security button then selecting the Trusted Sources tab. There's an option here to 'Trust access to Visual Basic Project'.

If you check this, VBA has access to itself and in theory you should be able to do what you're after - you'll be able to access the VBAProject object in your code.

However, you'll have to change this setting on everyone's machines and you'll open a security hole. I'd recommend you leave things as they are and train someone on how to install new versions of your file and just email it to them.

Nelviticus

 

[Chattin]

Why don't you distribute your macro as an add-in?

Also, for info, here's some info at Chip Pearson's site regarding programming to the VBE:

http://www.cpearson.com/excel/vbe.htm

Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help