Protect an entire workbook You can protect an entire workbookthe structure of the workbook, the window position and size, or both. Use the Review Protect Protect Workbook command for this purpose.
Lock objects on the worksheet Use the Properties section in the task pane to lock objects (such as shapes) and prevent them from being moved or changed. To access this section of the task pane, right-click the object and choose Size and Properties. Locking objects takes effect only when the document is protected using the Review Protect Protect Sheet command. By default, all objects are locked.
Hide rows, columns, sheets, and documents You can hide rows, columns, sheets, and entire workbooks. Doing so helps prevent the worksheet from looking cluttered, and it also provides some modest protection against prying eyes.
Designate an Excel workbook as read-only recommended You can designate an Excel workbook as read-only recommended (and use a password) to ensure that the file can't be overwritten with any changes. You make this designation in the General Options dialog box. Display this dialog box by choosing File Save As, choosing a directory, and then clicking the Tools button found on the Save As dialog box. Choose General Options to specify the appropriate password.
Assign a password You can assign a password to prevent unauthorized users from opening your file. Choose File Info Protect Workbook Encrypt with Password.
Use a password-protected add-in You can use a password-protected add-in, which doesn't allow the user to change anything on their worksheets.
Excel passwords are not foolproof
Be aware that Excel passwords can often be easily circumvented using commercially available password-breaking programs. Don't think of password protection as foolproof. Sure, it will be effective for the casual user. But if someone really wants to break your password, he or she probably can.
Making the application aesthetically appealing and intuitive
If you've used many different software packages, you've undoubtedly seen examples of poorly designed user interfaces, difficult-to-use programs, and just plain ugly screens. If you're developing spreadsheets for other people, you should pay particular attention to how the application looks.
How a computer program looks can make all the difference in the world to users, and the same is true of the applications that you develop with Excel. Beauty, however, is in the eye of the beholder. If your skills lean more in the analytical direction, consider enlisting the assistance of someone with a more aesthetic sensibility to provide help with design.
End users appreciate a good-looking user interface, and your applications will have a much more polished and professional look if you devote additional time to design and aesthetic considerations. An application that looks good demonstrates that its developer cared enough about the product to invest extra time and effort. Take the following suggestions into account:
Strive for consistency When designing dialog boxes, for example, try to emulate the look and feel of Excel's dialog boxes whenever possible. Be consistent with formatting, fonts, text size, and colors.
Keep it simple A common mistake that developers make is trying to cram too much information into a single screen or dialog box. A good rule is to present only one or two chunks of information at a time.
Break down input screens If you use an input screen to solicit information from the user, consider breaking it up into several, less-crowded screens. If you use a complex dialog box, you may want to break it up by using a MultiPage control, which lets you create a familiar tabbed dialog box.
Don't overdo color Use color sparingly. It's easy to overdo color and make the screen look gaudy.
Monitor typography and graphics Pay attention to numeric formats and use consistent typefaces, font sizes, and borders.
Evaluating aesthetic qualities is subjective. When in doubt, strive for simplicity and clarity.
Creating a user Help system
With regard to user documentation, it's a best practice to provide users with paper-based documentation or electronic documentation (or both). Providing electronic help is standard fare in Windows applications. Fortunately, your Excel applications can also provide helpeven context-sensitive help. Developing help text takes quite a bit of additional effort, but for a large project it may be worth it.
Another point to consider is support for your application. In other words, who gets the phone call if the user encounters a problem? If you aren't prepared to handle routine questions, you need to identify someone who is. In some cases, you want to arrange it so that only highly technical or bug-related issues escalate to the developer.
In Chapter 19, Providing Help for Your Applications, we discuss several alternatives for providing help for your applications.
Documenting the development effort
Putting a spreadsheet application together is one thing. Making it understandable for other people is another. As with traditional programming, it's important that you thoroughly document your work. Such documentation helps if you need to go back to it (and you will), and it helps anyone else whom you might pass it onto.
How do you document a workbook application? You can either store the information in a worksheet or use another file. You can even use a paper document if you prefer. Perhaps the easiest way is to use a separate worksheet to store your comments and key information for the project. For VBA code, use comments liberally. (VBA text preceded with an apostrophe is ignored because that text is designated as a comment.) Although an elegant piece of VBA code can seem perfectly obvious to you today, when you come back to it in a few months, your reasoning may be completely obscured unless you use the VBA comment feature.
Distributing the application to the user
You've completed your project, and you're ready to release it to the end users. How do you go about distributing it? You can choose from many ways to distribute your application, and the method that you choose depends on many factors.
You could just hand over a download link or thumb drive, scribble a few instructions, and be on your way. Or, you may want to install the application yourselfbut this approach isn't always feasible. Another option is to develop an official setup program that performs the task automatically. You can write such a program in a traditional programming language, purchase a generic setup program, or write your own in VBA.
Excel incorporates technology to enable developers to sign their applications digitally. This process is designed to help end users identify the author of an application, to ensure that the project has not been altered, and to help prevent the spread of macro viruses or other potentially destructive code. To sign a project digitally, you first apply for a digital certificate from a formal certificate authority (or you can self-sign your project by creating your own digital certificate). Refer to the Help system or the Microsoft website for additional information.
Updating the application when necessary
After you distribute your application, you're finished with it, right? You can sit back, enjoy yourself, and try to forget about the problems that you encountered (and solved) during development. In rare cases, yes, you may be finished. More often, however, the users of your application won't be completely satisfied. Sure, your application adheres to all of the original specifications, but things change. Seeing an application working often causes the user to think of other things that the application could be doing.
When you need to update or revise your application, you'll appreciate that you designed it well in the first place and that you fully documented your efforts.
Other Development Issues
You need to keep several other issues in mind when developing an applicationespecially if you don't know exactly who will be using the application. If you're developing an application that will have widespread use (a shareware application, for example), you have no way of knowing how the application will be used, what type of system it will run on, or what other software will be running concurrently.
The user's installed version of Excel
Although Excel 2019 is available, many large corporations are still using earlier versions of Excel. Unfortunately, there is no guarantee that an application developed for, say, Excel 2010 will work perfectly with later versions of Excel. If you need your application to work with a variety of Excel versions, the best approach is to work with the lowest versionand then test it thoroughly with all other versions.
Also, be aware of any security updates or new changes to Excel released with service packs (for stand-alone versions of Excel). If some of your users are on Office 365, be aware that Microsoft has adopted an agile release cycle, allowing it to release updates to Office 365 practically on a monthly basis. This is great news for those who love seeing new features added to Excel. It's not so great if you're trying to manage compatibility with your application. Although rare, some changes introduced in these releases can cause certain components of your application no longer to work as designed.
Compatibility issues are discussed in Chapter 21, Understanding Compatibility Issues.
Language issues
Consider yourself fortunate if all of your end users have the English language version of Excel. Non-English versions of Excel aren't always 100 percent compatible, so that means additional testing on your part. In addition, keep in mind that two users can both be using the English language version of Excel yet use different Windows regional settings. In some cases, you may need to be aware of potential problems.
We briefly discuss language issues in Chapter 21.
System speed
Although system speed and processing power has become less of an issue on modern PCs and devices, testing your application for performance and speed is still a recommended best practice. A procedure that executes almost instantaneously on your system may take several seconds to execute on another system. In the world of computers, several seconds may be unacceptable.
TIP
When you gain more experience with VBA, you'll discover that there are ways to get the job done and there are ways to get the job done fast. It's a good idea to get into the habit of coding for speed. Other chapters in this book can certainly help you out in this area.
Video modes
As you probably know, users' video displays vary widely. Higher-resolution displays and even dual displays are becoming increasingly common. Just because you have a super-high-resolution monitor, you can't assume that everyone else does.
Video resolution can be a problem if your application relies on specific information being displayed on a single screen. For example, if you develop an input screen that fills the screen in 1280 × 1024 mode, users with a 1024 × 768 display won't be able to see the whole input screen without scrolling or zooming.
Also, it's important to realize that a restored (that is, not maximized or minimized) workbook is displayed at its previous window size and position. In the extreme case, it's possible that a window saved by using a high-resolution display may be completely off the screen when opened on a system running in a lower resolution.
Unfortunately, you can't automatically scale things so that they look the same regardless of the display resolution. In some cases, you can zoom the worksheet (using the Zoom control in the status bar), but doing so reliably may be difficult. Unless you're certain about the video resolution that the users of your application will use, you should probably design your application so that it works with the lowest common denominator800 × 600 or 1024 × 768 mode.
As you will discover later in the book, you can determine the user's video resolution by using Windows API calls from VBA. In some cases, you may want to adjust things programmatically, depending on the user's video resolution.
CHAPTER 2 Introducing Visual Basic for Applications
IN THIS CHAPTER
Using Excel's macro recorder
Working with the Visual Basic Editor
Understanding the Excel Object Model
Diving into the Range object
Knowing where to turn for help
Getting a Head Start with the Macro Recorder
A macro is essentially Visual Basic for Applications (VBA) code that you can call to execute any number of actions. In Excel, macros can be written or recorded.
Excel programming terminology can be a bit confusing. A recorded macro is technically no different from a VBA procedure that you create manually. The terms macro and VBA procedure are often used interchangeably. Many Excel users call any VBA procedure a macro. However, when most people think of macros, they think of recorded macros.
Recording a macro is like programming a phone number into your smartphone. First you manually enter and save a number. Then when you want, you can redial the number with the touch of a button. Just like on a smartphone, you can record your actions in Excel while you perform them. While you record, Excel gets busy in the background, translating and storing your keystrokes and mouse clicks to VBA code. After a macro is recorded, you can play back those actions any time you want.
The absolute best way to become familiar with VBA, without question, is simply to turn on the macro recorder and record some of the actions that you perform in Excel. This approach is a quick way to learn the relevant VBA syntax for a task.