Export a sheet as a PDF

It takes a few clicks but it is possible

I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)

Excel Cell Comments and Documentation

Macro to Create a Comment Report for a File

I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.

Ron DeBruin

Ron DeBruin is an Excel MVP and has been for a long time.

His site has lots of excellent macro code and free Add-ins.

He has tips and macro examples for the Mac as well as Windows.

I have used his pdf creator code to create pdfs of certain sheets in a file.

If you want to learn about modifying the ribbon he has some great tips and examples.

He also has code examples for amending Excel’s pop-up menus.

 

VBA – Easy Way to Handle TRUE/FALSE Tests

Using a boolean variable

Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.

Japanese Yen Format in Excel

Applying it quickly

To apply the Japanese Yen format can take quite a few mouse clicks.

The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.

Sub JapaneseYen()

Selection.NumberFormat = "[$¥-411]#,##0.00"

End Sub

If you are unsure how to use macros, see the link below.

How to use macros

 

VBA AutoComplete

When you are typing code in the VBA code window you can press

Ctrl + Space Bar

to have Excel finish the word for you. Eg type

appli

And then press Ctrl + Space Bar to have Excel finish the word Application.