Tag Archives | Excel

Create a memory-efficient Data Model using Excel 2013 and the PowerPivot add-in

In Excel 2013, you can create data models containing millions of rows, and then perform powerful data analysis against these models. Data models can be created with or without the PowerPivot add-in to support any number of Pivot Tables, Pivot Charts, and Power View visualizations in the same workbook.

The data models can contain a huge amount of data that can use up computer memory which in turn could affect other programs that use the same system resources. If you use any Office or Excel online cloud applications, files are limited to 10MB which can be reached very quickly.

There is a good article here to read at on Microsoft’s Office Support website that will explain what you can do in detail.

You could also download and run Microsoft’s Workbook Size Optimizer. It analyses your Excel workbook and if possible, compresses it further.

Printing Excel Cell Comments

In Excel, how do you print cell comments that you have made on a worksheet. Follow these instructions:

Activate the worksheet that contains the comments that you want to print.
1.   If you want to print the comments in place on the worksheet, display them by doing one of the following on the Review tab, in the Comments group:

  • To display an individual comment, either click the cell that contains the comment, and then click on the Show/Hide Comment button, or right-click on the cell and then select Show/Hide Comments on the shortcut menu.
  • To display all comments, click on the Show All Comments button. You might need to move and resize any overlapping comments.

2.   On the Page Layouttab, in the Page Setup group, click on the Page Setup dialog box launcher .
3.   Click on the Sheet tab.
4.   Click on the Comments drop-down selector and select either As displayed on sheet or At end of sheet as required.
5.   Click on Print (you can always use Print Preview first to check everything looks OK).

Using the R1C1 Reference Style in Excel

There is an option within Excel to use the R1C1 reference style. This is not normally done as most users are comfortable with using the usual Column Letter and Row Number format for references in formulas. However, this can be changed by going to Excel options, selecting the Formulas category and then ticking the box for the R1C1 Reference Style. When you have done this you will notice back in the Excel workbook that the column letter header row has changed to numbers to reflect the changed referencing style.

The diagram here show how you would enter a reference in cell C3 (the grey cell) to point to different cells around C3. For example to point to to one row above the current cell in the same column would require a reference of =R[-1]C whereas to point to the cell one column to the right in the same row would require =RC[1] as a formula.

Releative

So a positive number in square brackets after R means a number of rows below, a positive number in square brackets after C means a number of columns to the right. A negative number in square brackets after R means a number of rows above, a negative number in square brackets after R means a number of columns to the left.

If you want an absolute reference (the equivalent of the dollar signs before the Column/Row reference) then remove the square brackets and use a positive number. For example, to refer to the second cell down from the top row in the current column we use =R2C as a reference. If I switched back to normal referencing and was in a cell in column E this would show up in the formula bar as =E$2.

Using R1C1 Reference Style in VBA

When I am teaching Visual Basic for Applications (VBA) programming I encourage my students to use this style of referencing in creating formulas as it solves problems when trying to programmatically enter a formula and you don’t want determine which row the cell is in . For example, if my VBA code had just imported some data and I wanted to now place a simple SUM formula at the bottom of the data, what do I enter? Well, let us assume I am in the first blank row below the data and I am not converting this range of cells to a table.  Also the first row of data is a column heading/title so I only want to add up from row 2 downwards. The formula would be:

=SUM(R2C:R[-1]C)

Using VBA, let’s assume I am currently positioned in the cell where I want to place the formula, my code would be:

Activecell.FormulaR1C1 = "=SUM(R2C:R[-1]C)"

How to run Excel VBA at specific times

I have been asked a few times now if it is possible to schedule VBA code to run at either regular intervals or at specific times. Using a little VBA code you can use the Application.OnTime method to run a procedure automatically.

The OnTime method requires two arguments to run. The first argument is a specific date and time at which the procedure should run and the second argument is the name of the procedure to run. It is worthwhile knowing that you must provide the date and time to run the procedure rather than an offset from the current time.; however, as you will see this can be worked around.

The following code will run the ProcessData procedure at 12:45

Sub RunTimedActivity()
    'Schedule the ProcessData procedure to run at 12:45
    Application.OnTime EarliestTime:="12:45:00", _
	Procedure:="ProcessData"
End Sub

 

If you need to cancel an OnTime method, you must provide the exact time that the event was scheduled to take place. The following code cancels the previous timed activity.

Sub RunTimedActivity()
    'Stop the ProcessData procedure to running at 12:45
    Application.OnTime EarliestTime:="12:45:00", _
	Procedure:="ProcessData", _
        Schedule:=False
End Sub

 

If you don’t know the precise time, the only way to tell Excel to cancel the next OnTime event or to cancel all the pending OnTime events is to close down the whole application. Therefore, the best advice I can give is to either store the time at which the procedure is to run in a Public variable and use that variable’s value in calls to OnTime, or store the time in a cell that is not viewed normally. Also note that if the workbook containing the procedure to be executed by OnTime is closed before the procedure is run, Excel will open the workbook again before running the procedure and will leave the workbook open after the procedure is complete.

Scheduling a Procedure to Run Regularly

In theory, the OnTime method needs a precise date/time to run. So if you want to schedule a procedure to run on a regular basis then you need a recursive function call that calls itself again in the future. If you need to stop this regular activity make sure you record the next time somewhere. Either use an out of the way cell or a public variable. In the example below I have used cell IV1. The example runs a procedure every 2 minutes 30 seconds.

Sub RunTimedActivity()
    Dim datNextTime As Date
    Dim intHrs As Integer
    Dim intMins As Integer
    Dim intSecs As Integer
    Dim strThisProcedure As String

    'variables to hold elapsed time
    intHrs = 0
    intMins = 2
    intSecs = 30

    'If the procedure named below is not
    'in a standard module then include
    'module name before the procedure name
    'such as ThisWorkbook.RunTimedActivity
    strThisProcedure = "RunTimedActivity"

    'Determine next time to run
    NextTime = Time + TimeSerial(intHrs, intMins, intSecs)
    Range("IV1").Value = datNextTime

    'Schedule this procedure to run
    Application.OnTime EarliestTime:=datNextTime, _
	Procedure:=strThisProcedure

    'Call the procedure you want to run
    ProcessData
End Sub

 

The procedure to cancel the above activity would be:

Sub CancelActivity()
    Dim datNextTime As Date
    Dim strThisProcedure As String

    'If the procedure named below is not
    'in a standard module then include
    'module name before the procedure name
    'such as ThisWorkbook.RunTimedActivity
    strThisProcedure = "RunTimedActivity"

    'Determine next time to run
    datNextTime = Range("IV1").Value 

    'Schedule this procedure to run
    Application.OnTime EarliestTime:=datNextTime, _
         Procedure:=strThisProcedure, Schedule:=False
End Sub

 

One final important note is that Excel will only run OnTime method when it is in Ready, Copy, Cut or Find mode. Any OnTime method cannot run at its scheduled time if you are in the process of editing a cell at that time. An alternative in this situation is to specify a window of time for the OnTime method to run. The following code will rum at some point between 12:45 and 12:50. If Excel remains in Edit mode for the whole of that period the task will be skipped.

Sub RunTimedActivity()
    'Schedule the ProcessData procedure to run between 12:45 and 12:50
    Application.OnTime EarliestTime:="12:45:00", _
       Procedure:="ProcessData", LatestTime:="12:50:00"
End Sub

Inserting a Space Character In Text

Following on from my last post about finding space characters, I  was asked how to go about inserting a space character if someone had incorrectly typed in a person’s name and left the space out.

So for example say we had ChrisPage in cell A1 how could you insert the space between the first and last names?  It depends on how many spaces would be needed. In this case as there is only a first and last name so only one space. An array formula within Excel could be used. Here it is:

{=REPLACE(A1, 1+MIN(FIND(CHAR(64+COLUMN($A$1:$Z$1)),MID(A1,2,255)&
(CHAR(64+COLUMN($A$1:$Z$1))))), 0, " ")}

(All the above should be on one line!)

Remember, to use an array formula you must type in the text without the beginning and ending curly brackets, then press Ctrl+Shift+Enter

If, however, the person’s name was something like BillyJoSpears, the array formula would not work. What you could do however, is create a User-Defined Function using VBA. Open up the VB Editor (using ALT+F11) and then from the Menu Bar select Insert, Module. In this module create the following code:

Function InsertSpaces(strName As String)
    With CreateObject("vbscript.regexp")
        .Global = True
        .Pattern = "(\w)(?=[A-Z])"
        InsertSpaces = .Replace(strName, "$1 ")
    End With
End Function

The above VBA function uses the power of VB Script to give  a regular expression that when it finds an upper case character after the first one, it replaces the text before the upper case character with that text again followed by a space. This works for any length of text with any number of upper case characters so would work with multiple names, just First and Last names and so on. To use the function in an excel worksheet just type =InsertSpaces(Range) where Range is the cell reference that has the uncorrected text in it. So if cell A1 held the text

BillyJoSpears

and in cell B1 we typed

=InsertSpaces(A1)

you would see in B1 the text

Billy Jo Spears

 

Finding the Last Space Character in an Excel cell

I am often asked in my Excel Advanced User course if it is possible to extract just a person’s last name from a cell containing their full name.

Yes it is but it is not as simple as using the RIGHT function. As people can have middle names or initials the cell could contain more than one space character to deal with. Moreover, the text might contain leading or trailing spaces that need to be removed so the specific last space between names can be found.  This is done using the TRIM function.

The formula shown below removes leading and trailing spaces then, using the REPT function, substitutes for each space found repeating spaces for the number of characters in the trimmed text. With the now very expanded text that has lots of spaces in blocks within it, we use the RIGHT function to extract the text from the expanded string for the length of the original trimmed string will give the last name with a load of leading spaces. Finally the last TRIM function removes any leading spaces, leaving the last name intact. This formula also handles having leading and trailing spaces and also circumstance where there is no space in the cell. The formula assumes the full name is in cell A1. Please adjust the cell references to fit your circumstances.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), 
	LEN(TRIM(A1))))

(All the above is typed on one line!)

Excel’s Undocumented DATEDIF Gem

Many people if they want to calculate the period between two different dates would subtract the earlier date from the later date. As Excel stores dates as numbers this normally works. But what if you want to calculate the number of months, or years?  How do you account for leap years, or 30 day months? There is a function hidden in Excel that for years has remained undocumented by Microsoft. Even now that Excel 2013 is released to the public, it remains undocumented. I am referring to the DATEDIF function, which can be used in all versions of Excel.

The DATEDIF function calculates the difference between two dates. You can specify a variety of different intervals, such as the number of years, months, or days between the dates. This function is great for calculating ages.

The syntax is:    =DATEDIF(EarlierDate, LaterDate, Interval)

Where: EarlierDate is the first date,    LaterDate is the second date,   Interval is the interval type to return.

There could be occasions when an error message is displayed by Excel. They are:

  • If EarlierDate is later than LaterDate, DATEDIF will return a #NUM! error.
  • If EarlierDate or LaterDate  are not valid dates, DATEDIF will return a #VALUE error.
  • If Interval is not one of the items listed below, DATEDIF will return a #NUM error.

The Interval value should be one of the following:

Interval

Meaning

Description

m

Months Complete calendar months between the dates

d

Days Number of days between the dates

y

Years Complete calendar years between the dates

ym

Months Excluding Years. Complete calendar months between the dates as if they were of the same year

yd

Days Excluding Years. Complete calendar days between the dates as if they were of the same year

md

Days Excluding Years And Months. Complete calendar days between the dates as if they were of the same month and same year

If you are including the Interval string directly within the formula, you must enclose it in double quotes:

=DATEDIF(EarlierDate, LaterDate, “m”)

If you have the interval in another cell referenced by the formula, that cell should not have quotes around the interval string. For example, with the formula

=DATEDIF(EarlierDate, LaterDate, A1)       cell A1 should contain m not “m”.

And finally as an example, to calculate someone’s age use the formula:

=DATEDIF(DateOfBirth, DateToday ,”y”)

Excel Data Validation and Case-Sensitivity

I was working with a client recently on some VBA programming and the client was trying to limit data entries in a column of cells to one of four specific values. Now those of you who are familiar with Excel will immediately shout out “set up data validation”, and you would be correct. However, not entirely, as most people would create the set of entries in a range of four cells somewhere and set the Excel data validation to that range of cells.

While using data validation this way would work after a fashion it would not enforce the case of the entries. Those of you staying in Excel would probably not worry. I was working using VBA (Visual Basic for Applications) and the VBA code was case sensitive. Not only that but the client wanted to force the text entered by users to have the first letter of each entered word to be upper case.

So how do you force data validation to enforce the case of entered values. The answer is to enter the values literally in the Source box of the Data Validation dialog, separating each value with a comma, and not set the source to be a range reference. So if I was restricting my entries to the three major cities of England then I would type in the Source box:

London, Birmingham, Manchester

In this way London would be acceptable but london or lonDON would be rejected.

Of course, if you are working with a dynamic list then you have to stick with range references and use some other method (as I would in my VBA code) to enforce the case of entries, but the above is a simple solution for short, static lists.