Excel Programming Basics

[mpopkowski]

I need clarification on creating a spreadsheet. When one creates a spreadsheet does all the formulas, macros and resulting data have to be saved together in one file or can the resulting data be saved separately in excel. The idea is to allow a on looker to look at the data but not how it was determined.

Thanks,
Mark

 

[Yogi Anand]

Hi Mark:

Typically, the data, the formulas, and macros, if any, for a project will be all part of the project workbook(s). If your objective is to ensure that the viewer or the user of the file does not have access to the formulas, etc. (so the viewer/user can not mess things up), then a worksheet of the workbook, and also the workbook can be protected, optionally with a password.

Depending on what your objective is, you might be able to use some other features in EXCEL as well. However, even using Protection and Passwords in EXCEL, security is not a strong feature of EXCEL.

 

[HalfAce]

Hi Mark,
Taking into account what Yogi said, you can try the following. (Bear in mind that if a user is Excel savvy and wants in, they'll likely find a way.)

For your macros you can prevent the code from being viewed by protecting the project. In the VBE, right click on your project and select 'VBAProject Properties'. In the Properties window choose the Protection tab and click the box for Lock project for viewing. Now enter a password, (confirm it) and click OK.
You need to save the workbook, close it and re-open it. Now you need the password to see the code.

For your formulas, you can select the cell(s) with formulas and hit Format > Cells > Protection Tab, click the box for Hidden and click OK. Now when you protect the worksheet you can’t see the formulas in the formula bar. It just looks blank.

This what you’re looking for?
Dan

 

[mpopkowski]

Thanks for the help !
Guys