Higher level macro problem

[sodabrab]

Hi everyone,

I have my VBA code running like I want. The problem is that the spreadsheet I am making the macro for is overwritten every week with the new weeks data added on to it. I am not in charge of the process of overwriting. Is there a way that I can make another macro to give to the person in charge that will automatically add my macro to the new spreadsheet before they send the document out to everyone?

Thanks.

 

[venkat1926]

Is it possible to park the macro in the personal.xls file so that the macro can be used to any workbook

 

[sodabrab]

No, I don't think I would want anyone to be able to run it. I decided to make a new workbook that when the user clicks a button I made, it copies the current data from the other workbook that gets overwritten and does all the calculations in the new workbook. That seems to be working nicely.

However, I'm still a little confused on where I should be keeping my code. Should each "function" of my code be split into different modules? Then I would just call that module when I needed it by saying run MyMacro() (or whatever the syntax for VBA is) Right now, pretty much all of my code (about 200 lines) is in Module1

I'm also confused on moving between modules. Can you pass variables into different modules? How do you reference a variable that is in Module2 while working in Module1?

 

[venkat1926]

regarding variable and module this quote will be useful

If we had wanted the string variable to be available to all of the procedures within our module, the variable declaration would have been made outside of the procedure:

Dim Msg As String
Sub HelloWorld()
Msg="Hello World!"
MsgBox Msg
End Sub
</pre> Finally, to make the string variable available to all modules within the workbook we need to use the 'Public' keyword and to declare the variable outside of a procedure:

Public Msg As String

refeence:http://www.techbookreport.com/tutorials/excel_vba2.html
</pre>regarading first problem

Instead of copying the workbooik why not save it in anohter name in which case the modules also will be saved.

to protect your code from using by others see this webpage

http://www.ozgrid.com/VBA/protect-vba-code.htm