The power of these Excel VBA commands/functions

I just finished a NEW Excel VBA video that shows you:

  • How to find what Excel VBA commands (Visual Basic commands) are available for you to use for a particular situation
  • How to handle the vastness of Excel VBA keywords efficiently
  • And much more…

Stop missing all the Excel VBA tools that you can use today to write your Excel macros faster.

It’s just 12 min long. Watch it below…

And download the list of all the Excel VB commands below…

{filelink=2}

Excel VBA productivity shortcuts – FREE download

When you want to save time doing little things here and there in Excel VBA Macros, shortcuts are extremely helpful.

The problem with most “Excel VBA productivity shortcuts” is that they are laid out in a way that is difficult to navigate and use.

TODAY you will get a handy and FREE Excel VBA shortcuts report that is laid out in a way that you can clearly see what the shortcut does FIRST, and then see the actual shortcut itself so that you are not tirelessly searching through the list.

This will also empower you to find the VBA shortcuts that you really need INSTEAD of compiling a list of things that you never use.

Click the link below to download my Excel VBA productivity shortcuts report for absolutely nothing:

{filelink=1}

I know you will enjoy this list and that it will help you greatly. Even if you are not using Macros that much now, you can keep it for future reference.

How to make your R1C1 VBA formulas easier to Read

You hate those horrible R1C1 formulas that your Excel recorder produces, right? Like this one…

Range(“B11”).FormulaR1C1 = “=SUM(R[-9]C:R[-3]C)”

Me too!

And the reason we hate that notation that much is because we are so accustomed to work with “A1” formulas in Excel and because this style of cell referencing is friendlier.

Here’s a nice trick (Convert formula method) to convert R1C1 formulas into A1 formulas easily.

For example: to convert this awful formula =VLOOKUP(RC[-1],R[-2]C[-6]:R[2]C[-4],2,1) into its “A1” equivalent, use the ConvertFormula statement this way…

1) Open the VBA editor by pressing CTRL+F11

2) Launch the Immediate Window

Do it this way…Press CTRL+G or go to View>Immediate Window…
Press CTRL+G

3)In the Immediate Window enter:

debug.print Application.ConvertFormula( formula:=”=YOUR_R1C1_FORMULA_GOES_HERE”, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

For our example today, enter this…

debug.print Application.ConvertFormula( formula:=”=VLOOKUP(RC[-1],R[-2]C[-6]:R[2]C[-4],2,1)”, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

You can use any Excel formula here
Immediate window, Application.ConvertFormula

4) and then press Enter

so you the immediate window returns =VLOOKUP(G16,B14:D18,2,1)

Press enter and get the results
Immediate window results

Did I leave something out? Please tell us your ideas in the comments section below…

How to Delete Duplicate Files Using Microsoft Excel

There was a time when a 40 megabyte hard disk was considered luxuriously big, but with today’s consumption of digital media and common file sizes in the gigabytes, modern terabyte hard drives can fill up fast. By filling up our space we don’t only cause a file management headache, if let get out of control it can even harm system performance.

One way we can keep our storage clean and uncluttered is to keep an eye on duplicates. By storing only unique files we can at least know we are not wasting any precious space.

Finding duplicates is not always easy, plus the solution often requires downloading a tool to do it. Well, good news. We have developed a tiny Microsoft Excel spreadsheet that can do the job for you!

Using the Spreadsheet

First, download the spreadsheet from here. You will see there are three tabs. The first lists the files, next we have a backup of the formulas just in case. Last we have these instructions.

  1. CTRL+SHIFT+L to list all the files on your “C:” hard drive in the ‘List of files’ tab.List of files
  2. Once the files are listed and the related data is in place, you can use Excel’s filter function to show files that appear more than once. Column C (labeled ‘how many times this file appears’) shows the count for the number of appearances for the file. Filter the sheet to show rows only where column C is greater than 1 to get a list of just the duplicate files.
  3. Use the standard Excel filter to show all the rows except those with 1 in column C.
  4. When only duplicate files are showing, sort the entire sheet on the filename column (B). You can also find duplicate files for a specific file extension by filtering all other file extensions (column D)
  5. Finally, if you want to delete or rename a file, select the full file path on column A and press CTRL+SHIFT+SDelete a file

Summary

Once you start digging into what Microsoft Excel can do you might be amazed. We hope this little tool will help you keep your digital life that bit more organized!

About the author

Yoav is the CEO of Cogniview, a company that creates PDF to XLS conversion software.

Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.

For more Excel tips from Yoav, join him on Facebook or Twitter

How to Create Hyperlinks in Excel Formulas and Macros

If you have any spreadsheets where one sheet has some kind of list and the other sheet has some details on the items in the list, you will understand how confusing it can be navigating from one to the other. Microsoft Excel has the ability to create hyperlinks between items in two sheets?

In this article we develop a macro that creates a link between an item in a list and a corresponding worksheet.

To create the example we used two templates found in Microsoft Office Online

  • Employee Profile
  • Employee attendance tracker

We programmatically create the hyperlink from one cell to another cell so that the user can go from the attendance to the profile and back again.

How to Use the Macro

Download our example Excel file from here so you can follow along with us.

  1. In the employee profile sheet select the employee name cell.
  2. Press Ctrl-Shift-L
  3. A link to the the employee’s attendance sheet will be created.
  4. In the employee’s attendance sheet a link to the employee profile sheet will be created
    Link in Employee Attendance Sheet

How it works

The main trick of this macro is in the formula to create a hyperlink:

=HYPERLINK("[File Name]'Sheet Name'! Cell Name","Friendly Name")

Here we used a handy function called ColumnLetter in order to convert the Column number into a Letter. That code was taken from here: Convert an Excel column number to its string equivalent.

Summary

By hyperlinking between related information in your spreadsheets you could improve productivity and reduce errors by quite an amount. Anything that removes the hunting and searching process will allow your users to get right to the information they need. How else might you use this macro?

About the author

Yoav Ezer is the CEO of a company that produces PDF to XLS conversion software and also co-author of the technology and productivity blog Codswallop.

For more Excel tips from Yoav, join him on Facebook or Twitter.

How to Use Microsoft Excel to Calculate Salary

For small businesses who do not want to pay an outside service for payroll the first port of call is usually Microsoft Excel. Standard office type employees are pretty simple to keep track of, but what if your employees have overtime and other work structures? How do you calculate the correct pay?

Let’s take a look!

First download our example spreadsheet.

Instructions

  1. Fill the Rates Table.
    • Regular – the rate for regular hour
    • Nights & Weekend – the rate for nights and weekends
    • Over Time – the rate for over time hours
    • non over time hours – the number of “regular” rate hours, every additional hour will be calculated as over time
  2. Fill the Hours Table – for each day fill the number of working hours in day, night time or weekend.
    Work hours table in Excel
  3. Salary table
    Computed salary table in Excel

    • In the Salary table you will get the sum of regular weekendnights and over time hours
    • In the salary column you will see the salary calculation according to the rates in the Rates table
  4. Calculation – in this table you can see each day over time calculation

How does it work?

There is a lot going on in this spreadsheet but in actual fact it is relatively straightforward. Let’s start from the end.

Imagine you already have the number of regular hours night hours and overtime hours, calculating the salary is easy:

salary = #regular hours * regular rate + #night weekend hours * night weekend rate + #over time hours * over time rate

Now we have to calculate the number of working hours in the day, nights, weekends and over-time.

First we add 4 columns for each day: day, OT (day), night OT(night), in these columns we will write the hours that will be used in the salary calculation.

Lets assume that the non over time hours is 20, in this case, since the employee didn’t accede the non over time hours, his regular hours OT and night hours OT on Monday is 0:

Let’s look at different numbers:

In this case there are no OT hours on Monday morning, but since Monday day + Monday night = 22 , for the salary calculation we will count 10 hours at night and 2 hours as OT.

Let’s go over the formulas of Monday and Tuesday:
Monday Day:
IF(B8>=Non_OT_Hours_Rate,Non_OT_Hours_Rate,B8) -> if(Monday Day > Non_OT_Hours_Rate,Non_OT_Hours_Rate,Monday Day)

if the number of hours on Monday Day exceed the non OT hours number, then on the salary calculation we count only non over time hours as regular hours (the rest will be calculated as over time hours), if the number of hours doesn’t exceed the non OT hours number then we can count all the hours as regular hours when calculating the salary.

Monday Day OT:
IF(N8=B8,0,B8-N8) -> IF (calculated Monday Day = Monday Day, 0, Monday Day – calculated Monday Day)

If the number we calculated for Monday Day equals the number of hours in Monday Day it means that the number of working hours in Monday day didn’t exceed the OT limit and therefore the number of OT hours for Monday Day is 0. If not then the number of OT hours will be the difference between Monday day and calculated Monday day.

Monday night :
IF(O8>0,0,IF(SUM(B8:C8)>=Non_OT_Hours,Non_OT_Hours-B8,C8))-> IF(Monday Day OT>0,0,IF(Monday day + Monday night >=Non_OT_Hours,Non_OT_Hours-Monday day, Monday Night))

If Monday day OT is > 0 it means that we already passed the OT limit and therefore every extra hour will be calculated as OT. In this case the night hours will be 0. otherwise we have to check if the sum of Monday day and Monday night exceed the OT limit, if so, the calculated Monday night hours will be the (OT limit – Monday day hours) ( we want to count only the hours that doesn’t exceed the OT limit). Last, if the sum of Monday day and Monday night doesn’t exceed the OT limit, the calculated Monday night hours will be equal to Monday night hours.

Monday night OT :
(P8=C8,0,C8-P8)
Same as Monday day OT

Tuesday Day:
IF(SUM(Q8,O8)>0,0,IF(SUM(B8:D8)>Non_OT_Hours,Non_OT_Hours-SUM(B8:C8),D8)) -> IF(SUM(Monday day OT , Monday night OT,O8)>0,0,IF(SUM(Monday day ,Monday night, Tuesday day)>Non_OT_Hours,Non_OT_Hours-SUM(Monday day, Monday night),Tuesday day))
sum(a,b) is a short way to write : or(a>0,b>0) , on Tuesday day calculation we first check if there were no over time hours until now, if there were, then the calculated Tuesday day will be 0 (the hours will be counted as over time), if not we check if the sum of the hours until Tuesday morning exceed the over time limit. Tuesday day hours will be (non over time hours – Monday day – Monday night) otherwise, if we didn’t exceed the over time limit. The calculated Tuesday day will be equal to Tuesday day

Tuesday Day OT:
IF(R8=D8,0,D8-R8) -> IF(Calculated Tuesday day = Tuesday day ,0,Teusday day – calculated Tuesday day)
Same as 3.2 Monday day OT

Tuesday Night :
IF(SUM(O8,Q8,S8)>0,0,IF(SUM(B8:E8)>=Non_OT_Hours,Non_OT_Hours-SUM(B8:D8),E8)) – > IF(SUM(Monday day OT,Monday night OT,Tuesday day OT)>0,0,IF(SUM(Monday day , Monday night, Tuesday day , Tuesday night)>=Non_OT_Hours,Non_OT_Hours-SUM(Monday day , Monday night , Tuesday Day),Tuesday Night))

Same as Monday night : if we already have OT hours then calculated Tuesday night will be 0 (this hours will be added to the OT hours), otherwise we check if the sum of the hours until Tuesday night exceed the non over time hours limit, if it does, the calculated Tuesday night will be (non over time hours – Monday day – Monday night – Tuesday day). (the rest will be added to OT) , if not (we didn’t pass the OT limit) , the calculated Tuesday Night will be equal to Tuesday night.

Tuesday Night OT :
(T8=E8,0,E8-T8) -> (Calculated Tuesday night= Tuesday Night,0,Tuesday Night-Calculated Tuesday night)
Same as Monday day OT

Summary

This seems like a lot but providing you have the information there it is just a case of working through step by step. Take a look at our example spreadsheet and see how you can modify it for your own needs!

About the author

Yoav Ezer is the CEO of a company that produces PDF to XLS conversion software. He also co-authors the technology and productivity blog Codswallop.

For more Excel tips from Yoav, join him on Facebook or Twitter.

Editing Excel Content in Word

A reader asked us if there was a way to edit the contents of a cell in Excel, using an external program, for example Microsoft Word. This was an interesting challenge and we could see how there were many possible uses. Here is how we solved it!

Using Our Solution

If you want to see our resulting spreadsheet and follow along, go grab the example Excel file.

To make it easy to use, we bound the macro EditSelectedCell to a keyboard combination (Ctrl-E).

To launch Word and edit the cell content, put your cursor in the cell you wish to edit.

Now use the keyboard combination to launch the macro. When you are in Word, type your text.

When you are done, close your document.

Your cell will now be populated with the text you typed in Word.

How it Works

Each cell editor is an object of class CellEditor, which wraps a Word application and document, as well as a reference to the edited cell.

'' The Word application for this editor
Private WithEvents wApp As Word.Application
'' The Excel edited cell by this editor
Private editedCell As Excel.Range
'' The Word document for this editor
Private wDoc As Word.Document

Each cell editor handles the Word application’s WindowActivate and WindowDeactivate events in order to copy the cell contents from Excel to Word and back, so it appears to the user as if the editing is done inside Word.

Private Sub wApp_WindowActivate(ByVal Doc As Word.Document, ByVal Wn As Word.Window)
    '' As soon as Word appears, copy the cell contents into the document
    wDoc.Range.Text = ExcelToWord(editedCell.Formula)
End Sub

Private Sub wApp_WindowDeactivate(ByVal Doc As Word.Document, ByVal Wn As Word.Window)
    '' As soon as Word disappears, copy the cell contents back to Excel
    editedCell.Formula = WordToExcel(wDoc.Range.Text)
End Sub

In order to allow the user to close the Word application without saving, the CellEditor object also handles the Word application’s DocumentBeforeClose event, and closes the application without saving.

Private Sub wApp_DocumentBeforeClose(ByVal Doc As Word.Document, Cancel As Boolean)
    '' Prevent application from asking the user to save. Instead just close
    wDoc.Close (False)
    wApp.Quit (False)
    Cancel = True
End Sub

The CellEditorStatic module

In order to prevent creating more than one editor of the same cell, the CellEditorStatic module holds a dictionary of all active editors.

'' Dictionary of existing editors for cells
Private editorMap As Object

The dictionary object itself is initialized when it is first accessed.

An existing editor is simply activated using Activate instead of creating a new one, thus bringing it to the front of the UI.

If (editorMap.Exists(CellKey(cell))) Then
    editorMap(CellKey(cell)).Activate
Else
    editorMap.Add CellKey(cell), New CellEditor
End If

FinishEditCell handles the case when an editor closes (Word application’s Quit event), in order to remove the editor from the dictionary.

If (editorMap.Exists(CellKey(cell))) Then
    editorMap.Remove CellKey(cell)
End If

Note that the cell’s key in the dictionary is its address in external form (e.g. [DocumentName]SheetName!A2 ), so that editors of cells in different sheets do not collide.

Private Function CellKey(cell As Excel.Range)
    '' Computes the key to a cell in the dictionary
    CellKey = cell.Address(, , , True)
End Function

Summary

Although this is a simplistic article just to keep things simple, it does demonstrate how Microsoft Office allows far more integration between applications than it at first might seem. How might you use this feature? We would love to know your ideas …

About the author

Yoav is the CEO of a company called Cogniview that creates software to convert files from PDF to XLS.

Prior to that, Yoav Ezer was the CEO of Nocturnus, a technology-centered software solution company.

For more Excel tips from Yoav, join him on Facebook or Twitter