• Downloads - Learning Aids

    by Published on 2011-05-05 05:13 AM     Number of Views: 33323 

    Please Note: This text is the complete reproduction of Chapter 17 - Security if Microsoft Office from my book RibbonX: Customizing the Office 2007 Ribbon, and is reproduced with permission of Wiley Publishing who retains the copyright of this work.

    This chapter was obviously written for Excel 2007, and still holds true for Office 2010.

    In addition, this chapter also discusses macro security applicable to Office 97 through Office 2003. Key portions to read for these versions are Security Prior to Office 2007 and Digital Certificates. While some of the screens may look a little different, they should be close enough to help you understand how to set up a SelfCert digital certificate and add it to your projects. These steps will allow you to avoid macro warnings in your programming environment, without having to sacrifice security by setting your security levels to low. ...
    by Published on 2008-05-01 08:32 PM     Number of Views: 20631 

    What are Styles?
    A style is pre-defined set of formatting instructions held together in one convenient package. They can be configured to apply one, two or many formatting characteristics, and can be applied to a worksheet cell or range very easily.

    Why use Styles?
    The biggest reason that you'd want to use styles is their ease of use when you want to update a cell, be it a color/colour change, number style or font. If you have a style set up, you simply need to change it in one place, and every other cell in your entire workbook that uses that style is updated with it. ...
    by Published on 2008-01-16 07:11 AM     Number of Views: 29495 

    I'm very pleased to be able to share a Chapter from my new book, RibbonX - Customizing the Office 2007 Ribbon.This chapter discusses the comboBox and dropDown RibbonX elements in detail. From using Microsoft's built in elements to creating your own with XML and programming the callbacks with VBA, it displays how to work with them. As you'll see, we provide practical examples for working with the elements in Excel, Word and Access so that you can follow along. ...
    by Published on 2006-10-20 05:28 AM     Number of Views: 51002 

    One of the biggest parts lacking in user constructed solutions is documentation in the form of help files. As coders, we usually find it easier to build the application than explain to someone how to use it properly. After all, what we write in code is rarely read by others, and most novices think it's gibberish anyway. Actually explaining what you do in plain English (or whatever language you use) can be quite difficult. Ironically, it is this very fact that tells us why help files are important... write it down once, nicely, and you should never have to explain it again.

    Office makes use of Compiled HMTL (chm) help files, which can be created by any number of applications. While writing these documents can be a difficult task of explaining yourself to the end user, the final trick is getting the compiled file to work with your application. This article was written to help ease the burden of the coding part, but unfortunately I'm going to leave the actual construction of the files to you, although I will give you some pointers to get started.

    Programs to build help files:
    First thing is first, you'll need to install a copy of Microsoft's HTML Workshop, if you don't have it installed already. This program is used by all Help authoring software, so it's not really an optional thing.

    The next step is to find a suitable HTML Help compiler. Personally, I use West Wind Help Builder. It has an excellent FAQ included on how to start using the program, and allows for creation of some very rich help files. I'm a big fan of being able to easily write my documents including screen shots, hyperlinks, and context IDs, and West Wind Help makes this fairly easy. It even integrates nicely with another fantastic product, Tech Smith's SnagIt, for capturing nice screen shots. There are trial versions of both products available at the links provided.

    Other programs do exist, but I cannot speak to their usefulness, as I have not worked with any of them extensively.

    Built in Help methods:
    Microsoft Excel does have some built in help constants and methods that you can use to deliver help in your applications. They work to a point, but they do have limitations. ...
    by Published on 2006-09-19 06:10 AM     Number of Views: 88949 

    This article gives two methods to fill a multi column listbox on a userform with data from an Excel worksheet. ...
    by Published on 2006-08-14 05:39 AM     Number of Views: 12357 

    This article shows the method I use to maintain versions and builds in my applications and add-ins, which is to add properties to the ThisWorkbook module. These properties store their values in custom document properties, accessed from File|Properties|Custom, as shown below:
    Benefits of this approach
    Some of the benefits you get by using this method are:
    • You get a place to store a value within the workbook, but avoid the need to store it in a worksheet cell. this can potentially save you creating a worksheet specifically for this purpose.
    • You can avoid the use of code to place information in the registry, which makes a workbook more portable between users.
    • You can store a piece of variable information between procedures. (Variables go out of scope after procedures complete.)
    • You can use intellisense to easily call up a property when coding

    Drawbacks of this approach
    • Users can change or delete these values if they know that they exist, or have reason to do so.

    Other uses
    The other uses for this are numerous, but one that stands out, ...
    by Published on 2006-01-11 10:28 AM     Number of Views: 22547 

    Macro Purpose:
    • Function to show the formula in another cell as text.
    by Published on 2005-01-04 09:32 AM     Number of Views: 21433 

    Function Purpose:
    This function can be used to retrieve one of three types of usernames, and can be used in a worksheet cell or in VBA.

    The usernames that it can return are:
    • Microsoft Office username (default) as defined in Tools|Options|General
    • Microsoft Windows logon name
    • Computer name (as defined in your system properties)

    by Published on 2004-12-20 08:09 AM     Number of Views: 60025 

    Macro Purpose:
    • Exports a table of data from Excel into a database, using an ADO connection to pass SQL strings.