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.