brentharvey.net

Don’t rely on locking/password protecting to hide your data!

Brent — Fri, 02 Jan 2009 20:19:23 +0000

I’ve seen this a few too many times. Someone wants to prevent the user of a spreadsheet from viewing detail data, so they hide the sheets (or cells), lock it, and password protect it. In about 4 line of code, that data is visible again:

Sub copyWorkbook()
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 01/02/2009
‘ Makes a copy of a workbook’s values for each workbook

Sub copyWorkbook()
    Dim sourceWB As Workbook, destWB As Workbook, sourceWS As Worksheet, destWS As Worksheet

    Set destWB = Workbooks(”Destination.xls”)
    Set sourceWB = Workbooks(”Workbook_that_has_the_data_you_want.xls”)

     For Each destWS In destWB.Worksheets ‘I like to keep one workbook in there, just to avoid problems
        If destWS.Name <> “__BLANK__” Then
            destWS.Delete
        End If
    Next destWS

    ‘Here’s the important stuff
    For Each sourceWS In sourceWB.Worksheets
        destWB.Worksheets.Add().Name = sourceWS.Name
        sourceWS.Range(”A:IV”).Copy (destWB.Worksheets(sourceWS.Name).Range(”A1″))
    Next sourceWS

End Sub

Some automated graphing

Brent — Mon, 05 May 2008 20:29:16 +0000

Heres some code I’ve used in one form or another to add charts for multiple worksheets that have the same layout (like monthly breakdowns of data)

For Each wSheet In Sheets

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=wSheet.Range(”B4:AX32″), PlotBy:=xlRows
    ActiveChart.SeriesCollection(1).Delete
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:=  ”Graph_” & wSheet.Name
    ActiveChart.HasLegend = True
    ActiveChart.Legend.Select
    Selection.Position = xlRight
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.Deselect
    ActiveChart.Legend.Select
    Selection.AutoScaleFont = True
    With Selection.Font
        .Name = “Arial”
        .FontStyle = “Regular”
        .Size = 8
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .Background = xlAutomatic
    End With

    Next wSheet

Wharton article: Biased Expectations: Can Accounting Tools Lead to, Rather than Prevent, Executive Mistakes?

Brent — Thu, 20 Mar 2008 13:02:39 +0000

Here’s an interesting article (with links to underlying studies) from Knowledge@Wharton summarizing two studies by Gavin Cassar. In one, he observes that (unsuprisingly) ”internal accounting report preparation significantly improves forecast accuracy“. The catch is: “the accuracy benefits from internal reports preparation are only observed for firms with high uncertainty” in forecasting.

In his second study, he shows how some of these same tools can lead entrepreneurs to adopt overly optimistic outlooks for their company’s prospects. I wonder if these entrepreneurs are using sensitivity and risk analysis to vet their forecasts, or are they getting a number they like, and calling it quits?

Get data from a cell in a closed workbook

Brent — Mon, 17 Mar 2008 15:59:26 +0000

There are lots of ways to get data from other workbooks. This has helped when grabbing summary data for report summaries (see here for creating an email)

Example:

p = “\some\directory”
f = “Some fake file.xls”
s = “Sheet1″
a = “A1″
result = GetValue(p, f, s, a)

Private Function GetValue(path, file, sheet, ref)
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ Source: http://www.j-walk.com/ss/excel/tips/tip82.htm
‘ Retrieves a value from a closed workbook

Dim arg As String’ Make sure the file exists
If Right(path, 1) <> “\” Then path = path & “\”
If Dir(path & file) = “” Then
GetValue = “File Not Found”
Exit Function
End If

‘ Create the argument
arg = “‘” & path & “[" & file & "]” & sheet & “‘!” & _
range(ref).range(”A1″).Address(, , xlR1C1)

‘ Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Create an email and attach a spreadsheet

Brent — Wed, 12 Mar 2008 15:51:31 +0000

As long as you’re automating your reports, you might as well automate the email notification!

Example:

strHTMLBody = “Test Message,

This is a test. If this were not a test:” & _
“

There would be useful information here
There would perhaps be more information here

Thank you”

Call CreateNewMail(”recipients@somedomain.com;morerecipients@anotherdomain.com”, “ccedfolks@adomain.com”, “Test Subject”, strHTMLBody,”Some fake file.xls”)

Make sure you enable Microsoft Outlook 11 Object Library! (see code)

Sub CreateNewMail(strRecipients As String, strCCs As String, strSubject As String, strBody As String, Optional strAttachPath As String)

‘
‘ Sub CreateNewMail
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 08/24/2006
‘ Source:
‘ http://www.sitepoint.com/forums/showthread.php?t=406443
‘ Creates email message to send report
‘ ToDo:
‘ Check for and load Outlook object library
‘In Visual Basic click on Tools, References and tick Microsoft Outlook 11 Object Library

Dim myOlApp As Outlook.Application
Dim myItem As Outlook.MailItem
Dim myRecipient As Outlook.Recipient
Dim myCCRecipient As Outlook.Recipient
Dim myBCCRecipient As Outlook.Recipient

Set myOlApp = CreateObject(”Outlook.Application”)
Set myItem = myOlApp.CreateItem(olMailItem)
Set myRecipient = myItem.Recipients.Add(strRecipients)
Set myCCRecipient = myItem.Recipients.Add(strCCs)
myCCRecipient.Type = olCC
myItem.Subject = strSubject
myItem.HTMLBody = strBody ‘If you don’t want to use HTML Body, comment this line and uncomment the next
‘myItem.Body = strBody ‘If you would rather use HTML Body, comment this line and uncomment the previous
If (strAttachPath <> “”) Then
Set myAttachments = myItem.Attachments
myAttachments.Add strAttachPath
End If

myItem.Display

End Sub

Last day of month for a date

Brent — Thu, 06 Mar 2008 22:34:42 +0000

When you create a date with the day part of 0, excel uses the last day of the previous month.

=DATE(YEAR(A1),MONTH(A1)+1,0)

Number of days in the month for a cell:

=DAY(DATE(YEAR(A1),MONTH(A1)+1,0))

Eliminate prompt to update links

Brent — Fri, 29 Feb 2008 22:33:04 +0000

Not every post has to be about advanced concepts!
To turn off the “Do you want to update links” prompt from popping up every time you open a spreadsheet (you can still update links manually):

Edit | Links…

Lower left corner, click on “Startup Prompt…”

Select “Don’t display the alert and don’t update automatic links”

Dynamic Named Range

Brent — Fri, 29 Feb 2008 22:30:00 +0000

If you want to reference a range whose size changes (for pivot tables, for example)

Insert|Name|Define…

Name the range, and in the refers to box:

=OFFSET(’Sheet Name’!$A$1,0,0,COUNTA(’Sheet Name’!$A:$A),COUNTA(’Sheet Name’!$1:$1))

the $A$1 is the upper left of the range (change as needed)

The first “COUNTA()” is the number of rows, and the second “COUNTA()” is the number of columns.

THIS ASSUMES THAT THERE ARE NO BLANK CELLS IN COLUMN A!! If there are, you need to find another column with no blank cells, or insert one. Then just change the reference in the first “COUNTA()” to that column.

Regular Expressions in Excel

Brent — Fri, 22 Feb 2008 20:04:43 +0000

Regular Expressions are very powerful pattern matching tools that can be used to find, return, or even substitute string patterns.

To use these, you’ll need to enable the VB Reg-ex Library:

In Visual Basic Editor - Tools|References
Select “Microsoft VBScript Regular Expressions 5.5″

For Help with Regular Expressions, Google is your friend, but you might start here:

http://www.regular-expressions.info/reference.html

Here are a couple I have found helpful:

Public Function RegMatch(Source As String, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True) As Boolean
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘This function returns whether or not the Source string contains the pattern
‘ Modified from http://www.dailydoseofexcel.com/archives/2005/08/13/pattern-matching/

Dim reg As New RegExp
reg.IgnoreCase = IgnoreCase
reg.MultiLine = MultiLine
reg.Pattern = Pattern
RegMatch = reg.Test(Source)
End Function

This one returns the part(s) of the Source string that contain the pattern. Could also be modded for replacement, but I think the link contains examples that already do that.

Function RegExtract(Source As String, Pattern As String, Optional IgnoreCase As Boolean = True, Optional MultiLine As Boolean = True, Optional Glbal As Boolean = True) As String
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘ Modified from http://www.vbaexpress.com/kb/getarticle.php?kb_id=68
‘ For help with regular expressions: http://www.regular-expressions.info/reference.html

‘Dimension the RegExp objects
Dim RegEx As Object, RegMatchCollection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, OutPutStr As String

‘ create the RegExp Object with late binding
Set RegEx = CreateObject(”vbscript.regexp”)

‘ set the RegExp parameters
With RegEx
.IgnoreCase = IgnoreCase
.MultiLine = MultiLine
.Global = Glbal ‘Not sure what this does, don’t care right now
.Pattern = Pattern
End With

OutPutStr = “”
Set RegMatchCollection = RegEx.Execute(Source)
‘Loop through each match in the string and concatenate them
For Each RegMatch In RegMatchCollection
OutPutStr = OutPutStr & RegMatch
Next
‘Put the extracted match into the corresponding B value cell
RegExtract = OutPutStr

End Function

File name and path of current workbook

Brent — Fri, 15 Feb 2008 15:43:16 +0000

I’ve found this useful when working on files on a department shared drive. I link it to a custom button on my toolbar, so I can just click the button and copy the full filename to the clipboard.

Sub copyFilePath()
‘ Sub copyFilePath
‘ Author: Brent Harvey
‘ Email: Excel.Examples@brentharvey.net
‘ Date: 02/20/2007
‘ Source:
‘ http://www.cpearson.com/excel/clipboar.htm
‘ Copies path and file name of active workbook to clipboard
‘Requires Microsoft Forms 2.0 Object Library
‘Click on Tools, References and tick Microsoft Forms 2.0 Object Library

Dim MyDataObj As New DataObject
MyDataObj.SetText ActiveWorkbook.Path & “\” & ActiveWorkbook.Name
MyDataObj.PutInClipboard
End Sub