Guest post: Excel add-ins tutorial
First, I would like to thank Mary for giving me this opportunity to introduce my first Excel add-in on her website. It is an excellent programming resource.
My purpose in this blog entry is to describe the Data Filter Tool, an add-in I created for Microsoft Excel, as well as discuss some steps you can take to make sure your Excel add-in works properly.
This add-in does one thing: it filters a table of data and copies or moves matchingrows to a new workbook. For example, if you have a list of addresses in Excel, this add-in lets you filter by state and copy (or move) all the rows matching a specific state to a newly created workbook. Headers are always copied, even if you select the Move option, to preserve headers in the original data. It preserves formatting of the original data (bold, italic, etc) but copies values, not any underlying formulas. So I don’t recommend you use this tool if your data contains formulas; it’s mostly for tabular data like addresses, contact information, etc.
The dialog box is devastatingly simple: you just select the range to be filtered, the column and unique data item to base the filter on, select what option you want (Copy or Move) and then click ‘Apply’.
Let’s peek under the hood to see how the add-in operates, and hopefully that will give you some idea of how to create your own add-ins.
The first thing we do is some start-up checks to make sure we are in the right environment. Please note that some of the code I will demonstrate actually comes from “Microsoft Excel 2000 Power Programming with VBA”, written by John Walkenbach, ISBN 0764532634. We check the OperatingSystem Property of the Application Object to make sure we are in a version of Windows.
Function WindowsOS() As Boolean
If Application.OperatingSystem Like “*Win*” Then
WindowsOS = True
Else
WindowsOS = False
End If
End Function
Notice that even though the default value of a Boolean variable is False, I never assume that this will work properly so I always explicitly give a variable a value. We use this code in the Workbook_Open() event of the ThisWorkbook module as such.
If WindowsOS = False Then
MsgBox “This add-in is for Windows OS only.”, vbCritical
ThisWorkbook.Close False
Exit Sub
End If
This will close the add-in if someone tries to open it in a non-Windows setting. If your code is only intended for Excel 2000 or later, test for version compatibility with this code:
If Val(Application.Version) < 9 Then
MsgBox “This add-in will only work in Excel 2000 or later.”, vbOKOnly
ThisWorkbook.Close False
Exit Sub
End If
Before adding a menu item to the Data>Filter Menu, we always try to delete the menu, in case Excel did not close properly and the Workbook_BeforeClose Event was not able to run. I set an object reference to the Filter Menu like this:
Dim cb As CommandBarControl
Set cb = Application.CommandBars(”Data”).FindControl(ID:=30031)
Then we delete the menu, using an ‘On Error’ Statement to skip any errors, in case themenu item doesn’t exist, which is exactly what we want.
On Error Resume Next
cb.Controls(”Data Filter Tool”).Delete
On Error GoTo 0
If we passed all our startup checks, the add-in is installed and allowed to run.
In this post I showed you my new add-in and demonstrated some techniques for securing your own code. Hopefully you can use some of these techniques in your own add-ins.
To download the Data Filter Tool, visit http://www.codeforexcelandoutlook.com/DataFilterTool.zip
and of course check out my site at http://www.codeforexcelandoutlook.com/.
Enjoy,
JP
Building a Website with Drupal for Newbies - Building Your Site
This is the fourth in a series of posts on how to build a Drupal website from scratch. My apologies for the delay in getting this installment out, a lot has been happening on the home and work front that sort of slowed me down, but here we are.
In the first segment we introduced Drupal, and went over what we would need to work with Drupal. In the second part, we went over how to install Drupal, and finally, in the third section we set up the Drupal site, changed configurations, and did some other cool stuff. If you have followed the tutorials this far, you should have your website all set up, with your desired settings and names, headers, logos, etc in place. As you know, I am creating a website in tandem with this tutorial, and everything that I do on that site I will describe here in this series and also in future Drupal related posts. The website is http://drupalpad.maryspad.com, and you can check it out to see how far things have progressed and compare it to your site.
Now that you have your site configured, and all the basics in place, the next step is to start building your site. At this stage, our site is still very basic looking, with the Drupal front page introducing the site. For now, let’s leave that as it is. Soon I will show you how to put something else there when we delve into content creation and content management. For now, let’s add some important components.
In the admin screen, click “Site Building“. Here you will see four sections: Blocks, Menus, Modules, and Themes.
Blocks allow you to configure your layout, and set different things in different regions of your site. For example, you may want a list of rss feeds from a different website to appear on the left side, and an ad block to appear on the right side. All these can be set using blocks. The available regions available to you will depend largely on the theme you use for your website. Some themes are built with more regions than others. In the Blocks page, as I mentioned, you will see what regions are available to you highlighted or surrounded by a dashed border on the page. You will then be able to scroll through the enabled features on your site and assign blocks to regions. We will explore how to do this in this tutorial.
It’s important that you decide how you want your website laid out before you select a theme, because then you can select a theme that makes all the blocks and regions you want available.
Menus helps you to create and control the different menus through which users will navigate your site. The Drupal default install comes with the Navigation menu all set up on the left side. You can change or disable this as you please, and can even change what items appear in the Navigation menu. You can also create your own custom menus and place them in different regions as you please, and add items to them.
Modules add functionality to your website. There are many modules, some which come pre-installed, and many many more that you can download and add, to enhance and really make your website powerful. Some modules that come preinstalled are Forums, Aggregators, etc. We will be exploring and enabling some modules shortly. Installing new modules is usually pretty straightforward, and modules will come with instructions.
Themes are what determine the overall appearance of your site and distinguish it from another website. The default drupal 5.7 install comes with the Garland theme, which is the same theme that the Drupal website currently uses. You can change the theme by selecting and enabling a new theme, and making it the default. We will do this later as we continue creating our website. The Drupal install comes with a few themes, but you can explore and find many other themes at the Drupal website, and on other websites. A simple search in Google will yield many different sources of themes. You can also configure the Garland theme and have different colors.
Remember that changing your theme needs to be done when you’ve decided how you want your website laid out so that you can select a theme that enables the regions you need to place blocks on. For the meantime, we will keep the Garland theme on our site and change it a bit later.
There are some changes we can make immediately though without necessarily changing the theme, and these include changing the site logo and the favicon shortcut icon (referred to in Drupal as the Shortcut icon). We do this by doing a global theme configuration, which basically means that these changes will be applied regardless of the theme we’re using.
NB: I have seen some themes that override global theme settings, so when you enable a new theme, make sure to go through and make sure everything is as you want it.
OK, enough theory. Let’s make some changes to our website.
Making Global Theme Changes
While in the Site Building page, click Themes. You will see a list of available themes. Go into the default theme, in this case Garland, and click “Configure”. Only themes that have been enabled (square box ticked) are configurable. You will see the different configuration options available for your theme and you can explore them further. For now we want to change global settings that will apply to all themes, with the caveat mentioned above. So at the top, under Themes, you will see “Global Settings”. Click on that.
Here you will see the global settings, such as whether you want to the logo, site name, slogan, etc to appear. Make your selections. On the right, you’ll see the option to allow content to show “submitted by Username on date” information. For now, if you’re a Drupal newbie, leave this as it is, and we will revisit it when we start working on content.
Further down the page are settings that you will want to change to further give your site it’s own identity. The Logo image setting allows you to change the default Drupal Logo on the top left side of every page to your own logo, if you choose to have a logo. Uncheck the “Use the default logo” box. If you have an image on your computer that you want to use as a logo, you can upload it here, or else link to it if it’s on another location on your webserver using the path to custom logo.
PS: You may find that you’ll need to resize your image to fit the space designated by the theme. What I usually do is right click on the image on the logo image and select properties, which allows me to see what size the default image is and then resize my own image appropriately before uploading.
The next step is to change the shortcut icon (or favicon) if you want to use one. This the is the icon that will appear next to your url name in the browser bar and next to your website name in the browser tabs. Again, you need to have created a favicon already, and have it somewhere. Uncheck the “Use the default shortcut icon” box and upload or link to your own favicon.
Then click “Save Configuration” to save your changes, and then navigate to your website url to see the changes. You may need to refresh the page or empty your cache to see the changes.
Hint: At this point, if you’re tired of the blue garland look, you can make one more change to make your website not look so default. The Garland theme allows you to make some color changes, so that while you’re still working with the same theme, it does look a little different. Go back into themes, and configure Garland. You will see an option to change theme color. Some themes come with this functionality that allows you to change theme color. Select the colors of your choice, either by using the drop-down box to select a predefined color set, or by selecting Custom and changing colors for the different parts of the theme, the base color, the header, etc. Experiment to see what you like and voila! Your site looks even more unique!!
For my sample site, I have removed the logo and the favicon, and changed the theme color to blue. Perhaps later I will find a nice graphic to add as a logo that matches whatever theme I finally settle on, but for the moment I am content with having no logo or favicon until I have fleshed the site out some more.
Working With Modules
We have now built our site to the point where we have the website name, logo, and favicon all set up. Our website is starting to look distinct and more unique. We are now going to work with modules. Modules allow you to expand the functionality of your website. They are powerful and can make your website do a lot of wonderful things. Being a relative Drupal newbie myself, I am still exploring all the modules on the Drupal website and seeing how I can use them in different ways on my websites. As I mentioned, the default Drupal install comes with some modules preinstalled. Some of these are enabled and some we will enable on an as-needed basis. At the end of this tutorial, I will talk briefly about how to add new modules and enable them.
For now, let’s look at what modules are available and see what we can use for our new website. In the admin mode, select “Site Building” and click on “Modules”.
You can see the long list of modules available. It would be redundant and a waste of time for me to list them and their descriptions here. Take a quick look at them. You will see some of the modules depend on other modules and require them to be enabled, while other modules are required by other modules. or example, the Forum module requires that the Taxonomy and Comments modules be enabled, which they are by default.
Depending on what you want your website and website users to do, you can enable or disable these modules to create the functionality you want. For our sample website, we would like to be able to have feeds from other websites, such as the latest news headlines, appear on the front page. For this to work, we need the Aggregator module to be enabled. You could also use this module to show feeds from your blog, etc. We also want to be able to have user polls, where we will create questions and have users vote on them. We want visitors to be able to search our website for the information that we need, and we want a contact form so visitors can send us a message without knowing our email address. Contact information on a website is important because it allows your visitors to give you useful and sometimes uplifting feedback, to share their thoughts, ask questions, and even let you know when something is broken. It’s better not to have an email address on your website, because then spambots can pick it up and you end up with a lot of junk in your email. So a contact form is useful for this purpose. OK, so let us check the boxes for the modules we’ve decided we want: Aggregator, Contact, Search, and Polls. One more module that I want you to select is Path. The Path module allows us to use Url aliases. Remember in the earlier tutorial I mentioned that beyond creating clean urls that didn’t have the ?q characters in them, you could also make the urls search engine and user friendly by making them match the page content? This is what the Path module does. So select that one too.
Once they’re selected, scroll down the page and click “Save Configuration.”
Hint: If you’re in an adventorous mood, go ahead and select other modules that you want to play with. I just chose some basic ones to work with for the tutorial, but don’t let my wimpiness deter you. For example, you may be ready to jump right in and add a blog! Well, good for you, go for it!!
Hint2: There is another module, related to the Path module, known as the Pathauto module. The Pathauto module extends the path module by automatically creating the url alias based on your content title. You have to download this module from the Drupal website and install it manually as it’s not part of the default install, and in addition you need the Token module for this to work.
We will play with these modules as we continue setting up our site and creating content.
Extra: Adding New Modules to Your Drupal website
Adding new modules that are not part of the Drupal install (such as Pathauto and Token mentioned above) is a relatively straightforward process.
1. Download the module you want to use from the Drupal website onto your computer.
2. Unzip the archive into a folder, and see if there is a readme or install text file that describes how to install the module. If there is, follow the instructions to get the new module working on your website. If there are no instructions, the following shoud work.
3. Using your favorite FTP program, go into your website folder, navigate to sites, and then to all. You should upload all modules and themes into this folder. This comes in handy if you ever use Drupal’s multisite feature, (which is too advanced for me to cover in this series of tutorials). It’s also the recommended practice. In the Sites/All folder, create two folders (if they don’t already exist). Call one Themes, and the other Modules.
4. Upload the folder with your module into the Sites/All/Modules folder.
5. To make sure the module is successfully uploaded, go into the admin page, Site Building, Modules, and scroll down to see if the module you just uploaded is available. If you see it then the upload process was successful and you can now go ahead and enable your module.
In the next tutorial, we will look at content types and content management, and start adding some content to flesh out our site. We will also work with our menus and the modules we enabled to make our site more functional and usable.
Bonus: Change the front page content
Because we’ve come this far and the site is looking good, why don’t we go ahead and change the content on the first page so we get rid of that default Drupal stuff? Click on Create Content in your left side navigation menu. I will talk more about the different content types in the next installment, and go into more detail on content creation and management. For now, just click on Story, and then type in whatever you want. You could do an introduction to your website, such as I did at the Drupal Pad, or something else, whatever tickles your fancy.
If you scroll down the page, you’ll see right below the content editor several lines of text, these are collapsible regions with options that you need to look. Again, we will go into all these in more detail later. For now, click on Input Format. You only need to change this if you added any HTML formatting to your text, such as I did in adding links, in which case you should select Full HTML formatting. Don’t worry about the URL path settings for now or any of the others except for Publishing Options. Click on that and make sure the options “Published” and “Promoted to front page” are selected. Then click “Submit”.
If all goes well, you should see what you just typed with the text ” Your Story has been created.” at the top. If you now click on your website name in the header you should see your website as someone would see it if they typed your url into the browser.
Congratulations, your new website is taking shape! In the next tutorial we will look at content types, content creation, and content management and add some more content to our site, as well as using some of the modules we enabled earlier, plus some other cool stuff.
As always, I welcome any questions and feedbacks you have.
Care for some Java?
Then you need to visit Another Random Developer Blog. This is a Java development blog with some great tutorials and practical code tips, tricks, and snippets that you can actually use. Even though I am not a Java developer I’ve been following the tutorials on this blog keenly, and I know anyone who’s even remotely interested in Java will enjoy them too. So there’s another useful resource for all developers (and aspiring developers) out there.
As an aside, the fourth part of the Drupal tutorial series will be posted soon, but be sure to check out Another Random Developer Blog.
Building a Website with Drupal for Newbies - Setting up your Site
This is the third in a series of tutorials on how to build a Drupal website. In the first segment we introduced Drupal, and went over what we would need to work with Drupal. In the second part, we went over how to install Drupal, either using a script installer such as Fantastico, or manually. If you have followed the tutorials this far, you should have a clean Drupal install in its raw form, ready to go.
In this third installment, we are now going to start setting up our website, looking at and modifying some Drupal settings, and generally getting it to look more like a unique website. As I create this tutorial, I am going to be building a Drupal website, and putting the screenshots here so that you can follow along and see the website evolve as we go. Since the website will be evolving as the tutorials evolve, if you are reading this tutorial long after this post was written, the website may look nothing like the screenshots, but you will be able to see what we have done so far. Everything I do on the sample website I will describe here. The sample website that I am building to accompany these tutorials can be found at http://drupalpad.maryspad.com.
Assuming you successfully completed the install process as we described it, you can now log in using the user account that you created, which is the super user account, and start to build out your site. Remember that if you used a script installer to do your installation, the account was created automatically based on the information you gave. If you installed manually, then you need to create a user account, which will automatically be designated as a super user account. Log into your new website using this account, and let us start setting up our website.
I will outline here the steps I usually follow in the order I follow them. At this point, your website should be looking something like this:
Site Configuration - this is usually my first stop. This involves setting up my site name, creating clean urls, and setting up other things.
a: Setting up Site Information
To set up your site information, click on Administer, on the left hand side panel. Click on “Site Configuration”, and then “Site Information”. Here you can enter your site name as you want it to appear at the very top, enter a slogan and mission if you want to, and footer information for your website. The site slogan and mission information are really optional, and if you enter it, you have the option to enable whether or not this information will be shown using the theme configuration, which we will go into later. In my example site, I will call the site The Drupal Pad.
In the footer section, you can enter information that will appear all the way at the bottom of every page of your website. This is an excellent place to put any copyright information and link to any other other websites you want to. One really cool thing is that you can use HTML in your footer. In my example, I want to mention that this website is part of the Coding Pad, and also enter some copyright information, so I enter this information there like so:
©2008 The Drupal Pad in partnership with <a href=”http://codingpad.maryspad.com”>The Coding Pad</a>
Then because I want to credit Drupal for this excellent CMS, I also enter the text “Proudly powered by Drupal” with a link to the Drupal website.
You can also set how you want to have Anonymous users designated should they post a comment or such, if you enable anonymous posting. I usually change this from the default “Anonymous” to “Guest”, because I think Guest sounds much friendlier.
b: Set up Clean URLs.
The next step that I do is to set up clean urls. These are much friendlier for SEO purposes, and more importantly, they allow you to change how urls will appear in the browser bar. So instead of the url looking like http://mywebsitehasdirtyurls.com/?qnode134 it will be a lot cleaner, like so http://mywebsitehascleanurls/node/1. You can go a step further, as we will in setting up this website, to change the urls so they are directly related to the title of the page, which will involve enabling the path module, and entering aliases for each of our pages. The end result will be our urls looking like so: http://mywebsitehasurlsthatrock/drupal-tutorial. This is so much more user friendly isn’t it?
We will go into how to do all this in a while, and will need to enable a module for this, but for now go ahead and enable clean urls. Drupal will first check to see if your server supports clean urls, so when you first click Clean URLs under Site Configuration, you will see the two radio buttons to enable or disable the clean urls, and then this message:
Clean URLs:
This option makes Drupal emit “clean” URLs (i.e. without ?q= in the URL.) Before enabling clean URLs, you must perform a test to determine if your server is properly configured. If you are able to see this page again after clicking the “Run the clean URL test” link, the test has succeeded and the radio buttons above will be available. If instead you are directed to a “Page not found” error, you will need to change the configuration of your server. The handbook page on Clean URLs has additional troubleshooting information. Run the clean URL test.
Click on the link to run the test, after which, if the test is successful, the radio buttons will be enabled and you can select “Enabled” and then save configuration.
There are many other settings that you can play with under Site Configuration, but these will do for now. I usually leave the rest at their default settings when I am first setting up a website, and then I go back in and change them as needed. Once you’ve become a Drupal guru you can decide what you went to change early and do it, but for now we will start with these two.
One more note, if you are expecting to have traffic while building up your site, you can temporarily take your site offline and leave a message to let your visitors know that the website is under maintenance and will return. This is particularly useful if you’re migrating your site and Drupal and don’t want visitors to be met with your new empty default install. You can still work on your site while it is offline and put it back online as soon as you’re satisfied that it’s ready for general viewing. If it’s a brand spanking new site and you expect to be done in a short while then it’s not necessary to do this.
To take your site offline or put it back online, go under “Site Configuration, and click on “Site maintenance.”
If you do take your site offline, please remember to get it back online when you’re ready. It’s easy to forget to do this since as the admin logged in you can see it, and you may forget that your visitors can’t see it.
You can see the progress so far with the site name and footer on my sample site here. Your site and mine still look pretty raw and have the Drupal content on the front, and besides the site title and the footer, there’s not much else there to make it look like a website, but in the next part we’re going to change all that… patience is the key.
In the next segment of this tutorial, we are going to start building our site. We will enable modules, and add content, and make some more changes to the settings that will make our website begin to look more unique and to have some substance to it, all pretty exciting stuff!!
Building a Website with Drupal for Newbies - Installing Drupal
There are many resources out there on how to install Drupal, so I am going to go over this in brief in this post.
If your webhost provides you with control panel that has a script installer, check if Drupal is listed. An example of a script installer is Fantastico, which comes with cPanel. Using a script installer makes the process pretty painless. Make sure though that you have the latest version of any scripts you may install using a script installer such as Fantastico. Otherwise, you may want to visit the homepage and download the updates.
In our case, we want to install Drupal 5.7. Even though there’s a new version out (6.0), as of this posting a lot of the modules have not yet been updated to work with 6.0. So we’ll go with 5.7.
If you don’t have a script installer like Fantastico, or you just want to do the install manually, the process is a little lengthier but not too complicated. There are install directions at Drupal webisite, but I’ll break them down here for you:
1. Create a database and user, and assign the user to that database. Make note of the database name and username and password as you’ll need them.
2. Extract the drupal archive that you downloaded into a folder on your hard drive.
3. Using your ftp program, upload the files in this folder into your domain folder where you wish to install Drupal.
4. In your browser, type the name of your website where your drupal folder is sitting, and you will be prompted to enter your database information:
5. Enter the database configuration information from step 1, and then click on “Save Configuration.” If all is well, you will see this screen.
6. After this you will need to create a user account for yourself. This very first account that you create is designated by Drupal as the admin account and is a superuser account.
Your website is now installed and you’re ready to set it up, which we will cover in the next tutorial.
You can find more detailed installation instructions at the Drupal website (http://drupal.org/getting-started/5/install). If you have any problems doing this install you can post them here and we will discuss them, or you can also check the Drupal forums to see if there is a solution there.
In the next installment, we will walk through the process of setting up your new website.
Building a Website with Drupal for Newbies - Introduction
As promised in my post on Kings among Content Management Systems, I am creating a tutorial for newbies on how to create a Drupal website. Being a relative Drupal newbie myself, I figured this would be the best way for me to learn and share what I’m learning.
Several things I need to point out before we begin:
- This is not the first Drupal website I am building, so it’s not trial and error, at the end of this tutorial you will have a functional Drupal website and enough tools and ideas to play with to advance your Drupal knowledge.
- Why another Drupal tutorial? There are many many Drupal tutorials out there, some really good, some not so clear, so why another one? Well, before I decided to work with Drupal I had read a lot of things about how difficult it was, and about how steep the learning curve was. My first forays into Drupal weren’t very productive, and the tutorials weren’t very helpful. After digging some more I finally started to figure it out, so I decided to create a tutorial as I go, so that people new to Drupal have one more resource to follow.
- This is a newbie tutorial, at least at it’s outset, so if you’re an expert, you may find it boring, but I welcome any comments and corrections and contributions. But don’t leave, stay and see how this develops, at some point it may be useful to you.
I have several goals in writing these tutorials:
- For you to learn how to build a pretty functional website that you can add content to and mold into more, and have the room and capability to expand, which Drupal is very good at.
- That after this series of tutorials you’ll go on past your first website to learn more and build more and share your tips and ideas with me and others.
- That I will learn more from writing these tutorials and from any questions or issues that may arise as you explore your ideas.
Drupal is currently, as of this writing, at Version 6.0, which was released just recently. However, this series of tutorials is going to cover version 5.7, mostly because version 6 is still very new and not all the modules have been updated yet. You can use version 6 but I can’t really guarantee that everything will work as described in these tutorials.
So what do you need?:
- Somewhere to host your new website. You can use a free host and free subdomain if you wish, but if you want this to be a serious project, I suggest that you get a domain name and a hosting plan, because you never know where this project will go. You may end up building something that you want to hold on to and develop! You can try GoDaddy.com Hosting Plans
or Hostgator.
- A domain name, if you choose to go that route. You can get a cheap .info for starters. You can also choose to build your website on a subdomain of one of your main domains, which is what I will be doing throughout this tutorial.
- Drupal, which is free and can be installed using a script installer such as a Fantastico, or manually. I will describe all this in the next installment so don’t worry too much about it at this point.
- Inspiration - just look at some of the sample Drupal websites I posted in Kings among Content Management Systems to see what is possible, and to start generating your own ideas. You can also visit http://www.drupalsites.net/ to see an assortment of Drupal sites.
- A notebook and pen, if you like taking notes.
- Your favorite beverage and some snacks.
- A sense of humor.
What don’t you need to bring with you?
- Programming knowledge of any kind: While it would be nice to have, and it may come in handy at some points, it really isn’t necessary to bring any programming knowledge to the table. If you need to know something, such as how to create a database for the installation, and you don’t know it, I will either show you how to do it, or point you to a tutorial that explains it in a way that I think is easy to understand.
- A pet turtle: I have one, but that doesn’t mean you need one.
So get everything set up, and decide what you want your website to be about, and in the next installment we will start working on building a website with Drupal.
