CODEWITHSUNDEEP
vbaexcel
user1760110
user1760110

Reputation: 2336

Where Exactly To Store VBA

We receive Excel files daily from our field offices which I have to clean and re-format (about 110 columns and 500 rows-worth) using VBA.

I need to save my VBA as a macro so we can use it to clean up all the workbook we receive by running the macro and saving the edited sheet as a new worksheet by getting the name from UserForm Combobox items.

Where exactly should I store the VBA snippets? I mean when I open the Visual Basic panel, I have these three options:

enter image description here

If I am supposed to use options 1 or 2, how can I call the UserForm for saving the sheet?

Upvotes: 0

Views: 951

Answers (3)

ApplePie
ApplePie

Reputation: 8942

If you receive files that do not contain VBA and you need to apply the same code on those files all the time then I propose that you either save that code in your personal workbook.

You can see how to do that here: http://office.microsoft.com/en-ca/excel-help/copy-your-macros-to-a-personal-macro-workbook-HA102174076.aspx

This is nice because you can also tie it to keyboard shortcut or just have it always ready for you to use.

The disadvantage is that it will only be set up per user session per computer. What you can do is have that code all set up in a module and then import it into your personal workbook if you change session or if someone else has to do it.

Once it's done, you will not have to include the module in your files your receive again.

Upvotes: 0

L42
L42

Reputation: 19727

Use Module:
If your VBA code focusses on data summarization and manipulation I suggest you use a Module.(example is what you have described in your question).

Use Form:
If what you wan't to do requires a GUI(Graphical User Interface) then you'll have to resort to Form where you can design your GUI. (example is if you have many fields that the user needs to fill-up with distinct values in which you provide the choices)

Use Excel Object:
If what you wan't to do has something to do with events happening on Worksheet and/or Workbook, like for example whenever you add sheet the macro runs, or when you open or close the workbook the macro runs then you will have to write the macro in the Excel Object.

This is basically what i have in mind, hope this helps.

Upvotes: 0

bto.rdz
bto.rdz

Reputation: 6720

I Recomend you to use modules (Option B)

Option C goes with option B, ill explain, you can create a sub in a module in option B, then you can do:

UserForm1.show

In Option B I would writte this code, but before trying this i recomend you to understand a bit more of vba

sub ClearWBs()

'opening workbook
Workbooks.Open Filename:="c:\book1.xls"

'your code

'your code


'below code for saving and closing the workbook
Workbooks("book1.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

end sub

Upvotes: 1

Related Questions