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.

 

Activating a sheet in VBA

Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.