Speeding up VBA Macros in Excel

I have stumbled upon a good way to speed up macros in Excel. If you use the Application.ScreenUpdating = False argument at the beginning of the macro, then the macro will not display what it is doing. Then when you use the argument Application.ScreenUpdating = True at the end of the macro everything the macro has done will become visible. This works well in speeding up long and complex macros.

Problems with double sided printing in adobe acrobat pro

In my office we were having a problem printing double sided a document with a mixture of portrait and landscape pages. The solution was to un check the "Auto rotate and centre" box. What this does is to automatically print portrait pages in portrait and landscape pages in landscape. When you un check the box you will need to set the paper up yourself and for any pages that are on a different orientation you will need to manually rotate or print separately. This cured the problem for me.

Highlight Duplicates in Excel 2003

In Excel 2003 there isn't a way to highlight duplicate items so i have written this VBA script to do it for you, just copy and past the script below. You will need to sort the data before you can run this script. You can change the values in the colour argument to change the colour that the cells will be high lighted in.

Sub FindDuplicates()
'
' !!NOTE: You MUST select the FIRST cell in the column and
' make sure that the column is SORTED before running this macro!!
'
Application.ScreenUpdating = False

colour = RGB(255, 0, 0)
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = colour
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop

ActiveCell.Offset(-1, 0).Select

FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(-1, 0).Value
Offsetcount = -1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = colour
Offsetcount = Offsetcount - 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Offsetcount = -1
End If
Loop

Application.ScreenUpdating = True

End Sub

This macro will process one column at a time and it will process the entire column.

Move a Sheet in Ecxel using VBA

I recently found the need to make a little button that would copy an entire sheet from a template document into a target document the VBA code needed to simply select and copy a sheet is:

Sheets("sheet name").Select
Sheets("sheet name").Copy Before:=Workbooks("target workbook name").Sheets("sheet name")

Adobe InDesign 'Falure to PDF' error

I have created a template in Adobe InDesign for a style of document that is used quite frequently. Recently the completed documents have started throwing up a 'Failure to PDF' error when exporting to PDF. As far as I can tell the reason this happens is due to the security features related to any PDFs linked into the document. The copyright security features prevent the document from creating a new PDF incorporating any PDFs in the document. The best way to solve this I have discovered is to change the PDF files into jpegs, a really easy way to do this is to download Virtual Image Printer from www.download.com, it's free and creates pretty good quality jpegs, the relink all of the PDF files to the new jpeg version you have created, this should solve the problem. More info can be found on the Adobe Forums.

Rendering Glass and other Transparent Materials in Indigo from Sketchup

Lately I have been trying to render a few items that include some glass parts using the Indigo Render Engine. Every time I rendered the image the glass would come out black. I was using the Sketchup Background Black environment as i wanted to use my own light sources. By chance I tried to render the same image using the Sun and Sky environment and the glass magically became transparent. I have found that the preset glass material only works well with the Sun and Sky environment. If you want to use your own light source it's a bit more complicated.

First create a new colour that is the shade you want you glass to be, i.e. with a slight blue hint or tinted black etc. and make it a phong material (or use the car paint preset). Then create another new material, this one we will paint the surface with, make this a blend material and select the A material as none and the B material as you glass colour. you can adjust the transparency of the glass by changing the blend ratio of these materials (1 being opaque and 0 being completely transparent) i suggest a value of between 0.3 and 0.5 as this gives a good reflection and colour in the glass but maintains transparency.

Combining Cells in Excel

A while back I was asked if there was a way to combine cells in Excel. The answer is there isn't, what you have to do is write a formula in the destination cell that reads '=A& &B& &C& &...' or you can use the 'Concentiate' function. Either way the cells that you want to combine must still exist as discreet cells you simply add an extra cell to combine the others together in.

Creating a Date Stamp in Excel

Strictly speaking the date stamp in Excel is done by pressing Ctrl + ; however if you would like to place a date stamp automatically when data is entered into a specific cell the i find that the following formula works well.

=NOW()

This will place the current date and time e.g. 15/08/2008 13:57. The down side is that every time the sheet recalculates i.e. any time enter or return is pressed the date stamp is updated. The easiest way around this is to create a circular statement. For example if when data is entered in to cell A1 you want to place a date stamp in B1 then the following formula will work.

=IF(A1="","",IF(B1="",NOW(),B1))

However due to the circular reference which basically stop the cell recalculating an error will be returned. To stop this go to Tools>Option select the Calculation tab and tick the Iterations box. This will solve the circular reference problem.

Removing alert Messages in VBA

I had written a VBA script that copied information from one workbook to another, every time the info was copies an alert popped up that had to be confirmed. This was tiresome to say the least, after much research and a little help form the Microsoft Forums writing the line

Application.DisplayAlert = False

just before the 'paste' command prevents this alert from popping up.

3D Wii

I've just been show this awesome video of this guy who has basically reversed the Wii and turned it from being a 2D into a 3D device. Just check it out, you'll see what mean.
Wii Head Tracking