Spy Journal Excel Tips

Sunday, July 31, 2005

Short Cut keys for Macros

You can create your own short cut keys for Excel VBA Macros you write.
To do this go to Tools | Macro |Macros (ALT+F8) and then select the macro you want to create the shortcut for.
Click Options and then create the keystroke combination you want. I usually hold down the SHIFT key and a letter, eg SHIFT+H. This creates a keystroke combination of CTRL+SHIFT+H and when that is pressed this macro will execute.

The reason to use SHIFT as well as CTRL (which is automatic) is to avoid regular Excel shortcut key combinations.
Check my page of Excel shortcuts for a huge list of shortcuts.

Saturday, July 30, 2005

Sum all item prices except one

ExcelTip posted this useful information.

Problem:

Columns A:B contains clothing items and their matching price.
How could we create a formula that will sum the prices in column B of all the items except the jacket?

Solution:

Use the SUMIF function as follows:
=SUMIF(A2:A6,""<>Jacket"",B2:B6)

Items____Prices
Shoes____100
T-Shirt__20
Jeans____50
Jacket___150
Socks____5

Result 175

Thursday, July 28, 2005

Opening a read only file in write mode follow up

A few days ago I wrote an article requesting help for opening read only files in write mode using VBA.
Sjoerd kindly responded (with a wet fish slap around my head) of the obvious answer I couldn't see right in front of me.

Workbooks.Open Filename:="filename", IgnoreReadOnlyRecommended:=True

That worked of course!
Thanks Sjoerd

Wednesday, July 27, 2005

How many days til Christmas?

I was asked this today by my kids so in order to give an exact answer I write this equation.
=DATE(2005,12,25)-TODAY()
Now you too can provide the exact answer easily when they ask!

Tuesday, July 26, 2005

Coding the Office Assistant to appear - or not

Andrew posted this interesting piece of code on his website a while ago. I have been contemplating using it for some clients who need prompting through their data entry processes.
If the office assistnat keeps popping up in your applications then it was you!

Sub Speak()
With Assistant
.Visible = True
.On = True
End With
With Assistant.NewBalloon
.Text = "Yes Sire?"
.Show
End With
End Sub

Sub GoAway()
With Application.Assistant
.AssistWithAlerts = True
Select Case _
.DoAlert("", _
"Do you want me to go away?", _
msoAlertButtonYesNo, _
msoAlertIconNoIcon, _
msoAlertDefaultFirst, _
msoAlertCancelSecond,False)
Case vbYes
With Assistant
.On = False
.AssistWithAlerts = False
End With
End Select
End With
End Sub

Go to his website to view the screenshots.

Friday, July 22, 2005

Using Arrays in Excel VBA

I love to use arrays in VBA for manipulating large chunks of data in a very quick time.
I often need to take output from a mainframe system like SAP or Peoplesoft or MIMS. Output data is usually always in the same layout, thought the content may be different. Sometimes it comes out in print (PRT) format and this contains page headers every page break. Compiling this data into a database in Excel (columns and rows of fields and records) is necessary in order to work with it for reporting and charting.
I used to do this before I knew about arrays by recording in VBA the actions I needed to perform on the raw data amd then editing it so it could work every time.
However I found where I needed to manipulate sometimnes several thousand lines and lots of columns of data that these macro's became very slow.

Now I use arrays instead. The main reason that arrays are quicker is that all your manipulations can be performed using memory rather than on the spreadhseet itself.

For example to take a set of data that has every second line blank and delete the blank rows can be done numerous ways using the spreadsheet method but they are all slow especially once you have over 10,000 rows of data.

Sub delete_rows_slowly()
'macreo written by NEUROTech 22 July 2005
Range("A1").Select 'number of rows of data
For i = 1 To 50
If ActiveCell = "" Then
ActiveCell.EntireRow.Delete
End If
ActiveCell.Offset(1, 0).Activate
Next i
End Sub

The quick way in an array is as follows:

Option Base 1
Sub delete_rows_quickly()
'macreo written by NEUROTech 22 July 2005
Dim myrange1(), myrange2()
Dim lastcell
Dim i, x

Range("A50").Select 'identify last cell
lastcell = ActiveCell.Row
Range("A1:A50").Select 'number of rows of data
ReDim myrange1(lastcell, 1), myrange2(lastcell, 1)
myrange1() = Selection
x = 1
For i = 1 To lastcell
If myrange1(i, 1) <> "" Then
myrange2(x, 1) = myrange1(i, 1)
x = x + 1
End If
Next i
Range("B1").Select
Range(ActiveCell, ActiveCell.Offset(x - 2, 0)).Select
Selection = myrange2()
End Sub

Note, the "Option Base 1" statement must appear at the top of the module above all procedures. It will affect all arrays in the module. It makes the starting variable in an array number 1 not zero.

Tuesday, July 19, 2005

Finding the Nth value in a range

I thought I would post this one from Exceltip.com as it highlights the use of the ROW function. The ROW and COLUMN functions are very useful for returning the number index of the activecell.

Problem:

Creating a new list (List2), containing every 3rd value from List1 (Column A).

Solution:

Using the INDEX function as follows:
=INDEX($A$2:$A$13,(ROW()-2)*3+3)
Enter the formula in the first cell of List2, and copy it down.

List1___List2
A_______C
B_______F
C_______I
D_______L
E
F
G
H
I
J
K
L

Monday, July 18, 2005

Macros disabled in this project.

Micorosft Excel installs out of the box with macro security set to high. This means none of your macros will run while it is in this mode.
To change you will need a workbook open then go to Tools | Macros | Security and select one of the options.
High - no macros will run unless theu have been signed as trusted.
Medium - you will be prompted on opening the workbook whether you want to enable macros or not (Workbooks can have Auto Open macros which run on opening the workbook). This is the safest option as you can always disable macros if you are unsure.
Low - just open it anyway. Only use this option if you regularly open files of your own or that you know are safe.
If you move to a differnt machine on your network or reinstall office then you will need to reset this.

Thursday, July 14, 2005

Random Number Generation

The function RAND() can be used to generate a random number between 0 and 1.
However this isn't always necessarily useful on its own. Today I was asked to produce a dual random number generator that looked at two data sets and selected randomly from each.
The first had 5 items and the second had 6.

To solve this I created two lookup tables with the first or index column being the numbers (ranked in order) and the second column being the items. I named these ranges for ease of use in formulas.

Then I created a formula.
=CONCATENATE(VLOOKUP(INT((upper1 - lower1 + 1) * RAND() + lower1),table1,2,FALSE),"-",,VLOOKUP(INT((upper2 - lower2 + 1) * RAND() + lower2),table2,2,FALSE))

Note: upper and lower are the highest and lowest calues (can be a cell link) for each data set.

This is essentially the same formula twice, once for each data set, and joined using the concatenate function.

Tuesday, July 12, 2005

VBA open files that are read only

I use the Workbooks.Open filename command to open files in VBA. However for files that have been saved with Read Only turned on I haven't been able to figure out how to open them in Write mode.
The help file helpfully says how to open them in Read Only Mode using this syntax:
Workbooks.Open FileName:="Array.xls", ReadOnly:=True

However using the ReadOnly:=False option does not seem to make any difference; the file still opens as read only.

If anyone knows how to open Read Only files in Write mode please let me know.
At the moment I have resorted to allowing the "Do you want to open as Read Only?" dialog box to display and click No in each instance. This is frustrating when manipulating a large number of files consecutively.

Using #N/A in line chart data

When you have zero data in a line chart it distorts your chart because the zero is plotted on the chart as a valid data point. If however the zero is there because a formula derives it or there is missing data, then this is not the result you want.
There are two solutions.
The first only works if the data is entered as values and can be easily edited. Simply replace zeros with empty cells. This can be done using the CTRL H replace function.
The second way to do this is to use #N/A in the formula.
For example =IF(original_formula=0,#N/A,original_formula).

Apologies for the delay in posting.

Tuesday, July 05, 2005

3 Dimensional SUMIFs

John McGimpsey writes this excellent article about using SUMIF across multiple sheets.

Three dimensional SUMIF()s
XL does not do 3-dimensional ranges very well. Often there's a need for a conditional sum over a number of worksheets (perhaps for a summary sheet that contains conditional sums over 12 monthly sub-sheets). It would be nice to write on sheet Summary:

=SUMIF(Jan:Dec!$D$4:$D$100, A1, Jan:Dec!E$4:E$100)
Unfortunately, SUMIF() isn't up to the job. There are three alternatives that I know of...

The morefunc.xll add-in
With Laurent Longre's morefunc.xll add-in, you can use the THREED function, which coerces a 3-dimensional range into a single array:

=SUMPRODUCT(--(THREED(Jan:Dec!$D$4:$D$100)=A1), THREED(Jan:Dec!E$4:E$100))

Unfortunately, the add-in is Windows-only, so should not be used in cross-platform development, or in environments where the add-in may not be allowed or available.

Summing SUMIF()s on each sheet
This method takes advantage of the fact that SUM() can sum a single cell across sheets. On each sub-sheet, in an out-of-the-way location (say, cell Z1), enter

=SUMIF($D$4:$D$100, Summary!A10, $E$4:$E$100)

Then back on sheet Summary, enter:

=SUM(Jan:Dec!$Z$1)

This method has the advantage of being simple and easily followed six months down the road when you need to make some changes. Note that the SUM() works by position - if, say, Sheet Apr is moved to the left of sheet Jan or to the right of sheet Dec, it will no longer be included in the SUM().

SUMIF()s on the summary sheet
Of course, you can bring all the individual SUMIF()s onto the summary sheet. For instance, in cell Z1, put

=SUMIF(Jan!$D$4:$D$100, A1, Jan!$E$4:$E$100)

Similarly in Z2, substitute "Feb" for "Jan", until in Z12 you have "Dec!$D$4...". Name Z1:Z12 as "My_SumIfs", then enter

=SUM(My_SumIfs)

Alternatively, you can put the names of the sheets in Z1:Z12 and use SUMPRODUCT to sum an array of SUMIF()s:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$Z$1:$Z$12&"'!D4:D100"), A1, INDIRECT("'"&$Z$1:$Z$12&"'!E1:E100")))

The big disadvantage of this technique is that if you rename or add a worksheet (admittedly unlikely with months, but very likely if you're summing one sheet for each salesperson), you also have to update the list in Z1:Z12.

Saturday, July 02, 2005

Andrews Utilities 2

Andrews Excel Tips site has an excellent addin utility titled imaginatively Andrews Utilities 2.
It can be downloaded here.
It includes amongst a host of cool utilities the following:

SELECT ALTERNATE ROWS & COLUMNS
ROUNDED CORNERS IN COMMENTS
CHANGE COMMENT'S COLOR
PASTE AS VALUES OPTION ADDED TO REPEATING RANDOM NUMBERS
SELECT X NUMBER OF ROWS
SELECT X NUMBER OF COLUMNS
COPY SUM OF SELECTION TO CLIPBOARD
and lots more...

Copyright Notice. All documents and text contained in this web site www.spyjournal.biz and sub pages is copyright material of Jethro Management (c) 2004 unless noted as being copyright material of someone else. No public reproduction of the content on this site in any form is permitted without express written permission.

Unless otherwise expressly stated, all original material of whatever nature created by Tim Miller and included in this weblog and any related pages, including the weblog's archives, is licensed under a Creative Commons License.