Checking odd and even numbers in VBA

It is sometimes useful to be able to check whether a number is odd or even.

Below is a very simple VBA function that does exactly that:

Function IsOdd(x As Integer) As Boolean
'------------------------------------------------------------------------
' Procedure : IsOdd
' Author : Zypher.co.uk
' Date : 09-Feb-2012
' Purpose : Check whether a value is odd or even
'------------------------------------------------------------------------
   IsOdd = (x Mod 2) <> 0
End Function

As always, I hope it proves useful.

Append multiple text files together using the DOS command prompt

If like me you record log files for some of your Excel files or databases you'll know it's not unusual to have a directory full of log files. If you then decide you want to import these into Excel or a database so you can do some processing on them it's quite a task to open each in turn.  You could create a short macro to do this for you, but there is also another way...

Open the command prompt and remind yourself of what DOS looks like, then use the “for” command.

The syntax is simple enough:

    for <filename> in (<directory>) do <command> <filename>

Working with our directory full of (*.log / *.txt) files, we use the “type” command and then pass each file into a new file using the >> operator.

">>" Appends data to the end of the file.
">" Completely replaces the file with new contents.

Inconclusion then here’s the command you need to run.

    for %f in (*.log) do type “%f” >> NewFile.txt

This assumes you are in the directory containing the log files.  If you are appending *.txt files I'd advise you to use NewFile.log.  otherwise the command will append the new file to itself when it finds it in the directory !

As always, hope it proves useful.

Autofilter on a protected worksheet

You will quite probably have come across this problem already.  You need to protect the data in a worksheet for any number of reasons, but the recipient of the report wants to be able to use autofilter.
I've had this code for a while, but only recently needed it again, which reminded me to put it up here.

It's a very simple piece of code.  The code below applies a password to a worksheet, but leaves autofilter available.

   With Worksheets("YourWorkSheet")
      EnableAutoFilter = True
      Protect Password:="123", Contents:=True, UserInterfaceOnly:=True
   End With

As always, hope it proves useful.

Microsoft Excel 2010 and MSCOMCTL.OCX

Recently when working with Excel 2010 and certain userform controls some of my users have received the following error message:

"Component 'Mscomctl.ocx' or one of its dependencies not correctly registered: a file is missing or invalid"

Sometimes certain Microsoft Libraries can become unregistered when installing and uninstalling software. A common problem is the MSCOMCTL.OCX file.

If you receive the above error first search your drive for MSCOMCTL.OCX to see if you have the file. The file should be found in your C:\WINDOWS\SYSTEM directory or at C:\WINDOWS\SYSTEM32 if you are using Windows XP.  If the file is missing you can download it from: http://www.majorgeeks.com/files/mscomctl.zip.

Once it is there click START -> RUN and type "REGSVR32 MSCOMCTL.OCX" (without quotes) into the box to register this control.

You should find this fixes the problem.

Also relevant: http://support.microsoft.com/kb/2296116

Lotus Notes Password Sync

If you are unlucky enough to work for a company that uses Lotus Notes you may, when starting Lotus Notes, receive the message:

"Your windows account password does not match your notes password. To synchronize passwords, use File->Tools->User ID and click Set Password" .

If you do not need to sync your password and this message is just another nuisance provided by Notes it can be stopped. I have found several “How To’s” that say you need to re-install Lotus Notes to remove the functionality. This is not the case.  It can be done by following the instructions below.

Close down Lotus Notes.

Windows 2000 users:

Go to Start -> Control Panel -> Administrative Tools -> Component Services -> Services (Local) -> Lotus Notes Single Sign on.
Change the start-up type to disabled by selecting the line then right clicking on options. Stop the service by clicking on stop.
Restart Lotus Notes and you will be prompted for the user password normally.

Windows XP users:

Go to Start -> Control Panel -> Performance and Maintenance -> Administrative Tools -> Component Services -> Services (Local) -> Lotus Notes Single Sign on. 
Change the start-up type to disabled by selecting the line then right clicking on options. Stop the service by clicking on stop.
Restart Lotus Notes and you will be prompted for the user password normally.

Good luck and I hope this helps.

It’s been a while

Hi, it’s been a while since anyone has gotten around to posting anything. 

We’ve been busy with some work for various clients but hope to post some articles in the coming weeks.  Our aim is to post at least two articles a month so if you have any questions do let us know.  You can contact us by leaving a comment under this post, or feel free to visit our main site at zypher.co.uk and use the contact us form.

Using the Microsoft CDN or Google CDN for jQuery

As we develop new websites and try to introduce more user interactivity and animation effects it will become more frequent that we use some kind of JavaScript library.

Probably the most popular and most well-known is the jQuery library.  This post is going to show you how to link the library to your site.  We’ll also add a tip or two along the way.

For a jQuery tutorial click here for the w3schools page.

Adding the jQuery Library to Your Pages

The jQuery library is stored as a single JavaScript file, containing all the jQuery methods. It can be added to a web page with the following mark-up:

<head>
<script type="text/javascript" src="jquery.js"></script>
</head>

Remember that the <script> tag should be inside the page's <head> section. 

The above method requires you to download the jQuery file (see here) and store it on your server with your site.  Two versions of jQuery are available for downloading: one minified and one uncompressed (for debugging or reading).

Alternatively you can use a Content Delivery Network (CDN).  There are three CDNs available; jQuery CDN (via Media Temple), Google Ajax API CDN and the Microsoft CDN.

Google:
<script type="text/javascript" src=http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js>
</script>

Microsoft:
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.2.min.js">
</script>

jQuery:
<script type="text/javascript" src="http://code.jquery.com/jquery-1.7.min.js”></script>

 

A couple of tips:

• Have a read of 3 reasons why you should let Google host jQuery for you.
• Check out Use Microsoft CDN for JQuery or Google CDN with fallback