Archive for the ‘Microsoft Office’ Category
Somebody posted a comment on my Excel UDF Tutorial, and on reflection it appears he’d like to navigate a range in VBA. While I don’t see a purpose in that because it duplicates either the
HLOOKUP function (depending on whether you’re navigating a column or row).
Anyway, here’s how you’d do it in VBA:
Function Finding(lookupWord As String, lookupRange As Range, lookupColumn As Integer) As Integer ' Define variables. Dim lookupIndex As Integer Dim lookupMax As Integer Dim returnValue As Integer ' Assign values to variables. lookupIndex = 1 lookupMax = lookupRange.Rows.Count returnValue = 1 ' Print starting and ending index values. MsgBox ("[" + CStr(lookupIndex) + "][" + CStr(lookupMax) + "]") ' Loop until found, but exit when not found. Do Until lookupRange.Cells(lookupIndex, 1).Value = lookupWord ' Print diagnostic value to determine what's evaluated. MsgBox ("[" + CStr(lookupIndex) + "] [" + CStr(lookupRange.Cells(lookupIndex, 1).Value) + "]") ' Fail safe exit when no matching value is found. If lookupIndex = (lookupMax + 1) Then ' Assign a 0 (zero) as a false return flag value. returnValue = 0 Exit Do End If ' Increment counting index value. lookupIndex = lookupIndex + 1 Loop 'Return a 1 when the value is found and zero when it isn't. Finding = returnValue End Function
I only chose a
DO UNTIL loop because that was in the comment. It would be more appropriate to, from my perspective, use a
FOR range loop, like this:
For lookupIndex = 1 To lookupMax ' Loop contents. Next lookupIndex
Hope this helps.
Debbie asked a question about why a comparison wasn’t working in the Microsoft Excel
SUMIF function when the initial array value was empty. This post answers that question.
SUMIF function takes a lookup range, which anticipates a one column with a lookup value and another column with a value. The
SUMIFS function works along the same line but accepts multiple lookups against a series of lookup list columns.
This shows a simple model of the
The formula in cell
If you left off the $ symbol and made the comparison
"<"&E3, you could add the sums for any number of values that are less than a value in the same row (or counting index with named ranges). You could also make the inequality symbol a parameter with this modified formula:
Now, your analysis model lets you choose whether you want to look for the sum greater than or less than a specific number in the lookup range. Named ranges compare index values between the lookup range and comparison range values, which allows them to be in different rows or spreadsheets. The only problem is that the label has an inverted inequality sign. You can fix that by constantly editing the description or by simply replace the text of the label with a dynamic text string, like this:
=CONCATENATE("Comparison """,$E$5,""" Value")
After applying the fix to the label, it looks like:
When the lookup column contains all nulls, the same formula returns a zero, as shown in the illustration.
You'll also get an error flag, unless you unchecked the Flag formulas that refer to empty cells in the error checking options. The
IFERROR function can't be used to suppress this type of error.
Why doesn't the error return a null value? That's because an equality operator compares values in the array against a static or dynamic lookup value. While equality operators don't implicit cast values for comparison, they also don't compare null values. Only the
ISBLANK function lets you find an empty cell and it can't be used inside a
SUMIF function call.
Quite some time ago, summer 2008, I wrote a post about how you could embed an image in a cell comment. It was for the then current version of the product – Excel 2007. Here’s a User-Defined Function (UDF) in VBA to perform that trick that works in Excel 2010/2011. A comment on that older post fed my disappointment that Excel 2011 doesn’t even support the navigation but it does perform it with VBA. This includes the UDF to add an image and an ordinary Excel Macro to remove the image when you want to preserve the text.
Let’s assume you have a list of image files in a directory and that you’ve entered their fully qualified or absolute path values column
B of your worksheet. Now you want to load them as comment images in Column
A and insert a value in each column
A cell that describes the comment image.
Unfortunately, as I’ve explained before you can’t assign the image as a property of the cell (or more precisely, I’ve never found a way to do it). If this is wrong, please post the magic here for all to enjoy without a fee or registration.
The following UDF takes a string value to describe the image and a cell reference that holds a string value that holds an absolute file name, which is a logical drive letter (
C:\), a file path, and file name.
Function InsertCommentImage(title As String, cellAddress As Range) Dim commentBox As comment ' Clear any comments before attempting to add them. Application.ActiveCell.ClearComments ' Define the comment as a local variable and assign the file name from the ' cellAddress input parameter to the comment of a cell. Set commentBox = Application.ActiveCell.AddComment With commentBox .Text Text:="" With .Shape .Fill.UserPicture (cellAddress.Value) .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft End With ' Set the visible to True when you always want the image displayed, and ' to False when you want it displayed only when you click on the cell. .Visible = False End With InsertCommentImage = title End Function
A fully qualified address for the
cellAddress parameter on a PC would look like this in let’s say cell
While it would be like this for the
cellAddress parameter on a Mac OS X installation in cell
You would call this from a cell like this when the text is provided as a string and fully qualified file name is in cell
B1 of a worksheet named
Alternatively, you rewrite
InsertCommentImage() as follows, which takes a string for the cell value and a string for the absolute file name:
Function InsertCommentImage(title As String, absoluteFileName As String) Dim commentBox As Comment ' Clear any comments before attempting to add them. Application.ActiveCell.ClearComments ' Define the comment as a local variable and assign the file name from the ' cellAddress input parameter to the comment of a cell. Set commentBox = Application.ActiveCell.AddComment With commentBox .Text Text:="" With .Shape .Fill.UserPicture (absoluteFileName) .ScaleHeight 3#, msoFalse, msoScaleFormTopLeft .ScaleWidth 2.4, msoFalse, msoScaleFromTopLeft End With ' Set the visible to True when you always want the image displayed, and ' to False when you want it displayed only when you click on the cell. .Visible = False End With InsertCommentImage = title End Function
With the change of the second parameter, you would call the
InsertCommentImage() function with two strings, like:
Here’s how it would look if you’d put the formula in cell
This is a simple Excel macro for which you could create a button. You run it when you want to delete only the image comment from a cell. The macro works by highlighting the cell and running it. Naturally, you could wrap it in another Excel macro to navigate through the list and clean up a bunch of comment images at one time.
Sub RemoveComment() Application.ActiveCell.ClearComments End Sub
If you want to allow the macro to detach all comments for a range, you would rewrite it this way:
Sub RemoveComment() Application.Selection.ClearComments End Sub
As always, I hope this helps and furthers sharing information.
The original blog post with the images was causing a page loading delay, so I’ve moved the post to a page of it’s own. This link send you to the original post with how you setup MySQL Connector/ODBC driver, add a new data source in Windows 7, and a new data import source in Excel 2007. As with other “how-to” posts, it includes screen shots to clear up any ambiguity. I apologize for any inconvenience caused by moving the content.
If you want instructions for using Excel to access an Oracle database, check this older post. I’m working on documentation for a native Mac OS X and Excel 2011 solution and will update this when it’s ready.
As always, I hope this helps those trying to sort through how this works. Naturally, a Visual Basic for Applications (VBA) solution is a better alternative once you’ve set up the data source. This is also on my calendar for documentation.
While discussing the limitations of wild card comparisons in the Microsoft Excel’s
IF function, I resolved to share my
ISLIKE function. It acts like the
LIKE operator in SQL. You can’t use the word like as the function name because it’s a reserved word in Visual Basic for Applications (VBA).
The function lets you compare any string to see if it is contained in another string. The default mode is case-insensitive, and the override mode case-sensitive. The optional third element lets you override the default comparison method.
You should remember or note that a number
0 is always evaluated as false, and any other number (negative or positive) is always true. You can put the
ISLIKE function in a macro sheet or an Add-in library. I recently posted instructions on how to create your own Add-in library.
Here is the VBA code for the user defined
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
Function ISLIKE( inValue As Variant _ , hasValue As Variant _ , Optional caseSensitive As Integer = 0) ' Define a logical return variable and assign a default value. Dim logicReturn As Boolean logicReturn = False If caseSensitive Then ' Finds a case-insensitive value as a component in a larger value. If inValue Like "*" + hasValue + "*" Then logicReturn = True End If Else ' Finds a case-sensitive value as a component in a larger value. If UCase(inValue) Like "*" + UCase(hasValue) + "*" Then logicReturn = True End If End If ' Return the logical value. ISLIKE = logicReturn End Function
Line numbers 12 and 19 use the
"*", which is equivalent to the regular expression of
"\.*". I’ve no idea why they made this choice, and it’s not well explained on Microsoft’s web site covering SmartTags.
You can shorten the program and make it slightly less efficient by performing the case sensitive match twice. The following doesn’t rely on nested block
IF statements. You also must change the third parameter to a
Boolean or the
Not operator won’t work correctly.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
Function ISLIKE( lookupValue As Variant _ , containsValue As Variant _ , Optional caseSensitive As Boolean = 0) ' Define a logical return variable and assign a default value. Dim logicReturn As Boolean logicReturn = False ' Finds a case-insensitive value as a component in a larger value. If caseSensitive And _ lookupValue Like "*" + containsValue + "*" Then logicReturn = True ' Finds a case-sensitive value as a component in a larger value. ElseIf Not caseSensitive And _ UCase(lookupValue) Like "*" + UCase(containsValue) + "*" Then logicReturn = True End If ' Return the logical value. ISLIKE = logicReturn End Function
Somebody asked me, “How this can be useful?” That meant from their perspective, how is it useful beyond a comparison of cell values. A quick example of that additional utility can be demonstrated from an early post. In that post on replacing a
VLOOKUP function with a UDF, there’s compound logic used to find the occurrence of a substring in a string. The
ISLIKE function can simplify that logic.
Here’s that snippet of the code from that referenced post. The snippet evaluates whether the base string is greater than one character and checks the second character of the string to see if it is a
- (minus sign).
ElseIf Len(LetterGrades(i)) > 1 And Mid(LetterGrades(i), 2, 1) = "-" Then
You can replace the compound logic with a call to the
LIKE function. It would replace line 58 as follows:
ElseIf ISLIKE(LetterGrades(i),"-") Then
The logic of the function examines to see if the first parameter contains the second parameter. This is what is meant by like. In the following illustration, you examine if a string contains a substring (or is like a substring). The cell
A2 is also assigned a Range name. Range names can increase the readability of your models and functions. All but the last one perform case insensitive comparisons that find whether the substring in cell
A2 is found with cell range of the
B10 formula performs a case sensitive match.
Another logic comparison uses grades to see if a minus sign is found in them. The example compares the cell values in
A1 against the
B2 values. Returning true for one and false for the other cases in column
C of the respective rows.
Here are the calls to the
ISLIKE user defined function. The first one is case-insensitive and the second case-sensitive, but there’s no case sensitivity with a minus sign,
As always, I hope this helps. If you’ve comments or alternative approaches, please let me know.
Microsoft Excel supports macros but it also supports library functions, known as User Defined Functions (UDF). Library functions are less risky than macros because they must return a value to a cell. This is a quick tutorial, mostly for my students, but as usual for anybody who’s interested.
Microsoft Excel User Defined Functions (UDFs) are different than standard VBA macros. They’re behavior is restricted. You can’t access other cells in a workbook, and may only return a value (also known as an expression) to the cell that uses the formula. That having been said, they can dramatically hide the complexity of mega-formulas and remove them from the editing control of users.
Here are the steps to configure your Excel 2007 environment to work with Visual Basic for Applications (VBA) and UDFs. They show you how to display the developer ribbon, open a module for editing, save VBA code into a library (
*.xlam file), and add the library file as an “Add-in” library to your Excel 2007 installation. The name of the library will be the same as the Workbook where you created it.
- Displaying Excel’s Developer Tab
There are four steps to make this visible. They are:
- Choose the Office Button. You’ll see the following:
- Click the Excel Options button.
- Click the Popular tab if not highlighted (it’s the default). Inside the right side pane, click the Show Developer tab in the Ribbon check box to enable it, and click the OK button to set it.
- Click the Developer ribbon tab. Click the left most icon to launch the Visual Basic Editor screen.
- Open a Module in the Visual Basic Editor by clicking the Insert menu item and choosing the Module element.
- Copy the following function definition into the open Module. The name of the UDF will be the case-sensistive name of the function in the VBA module. That means you’ll now have a newSerialDate function in your list of functions when you click the insert function button.
I struggled to come up with a simple function to illustrate this and how you debug UDFs. A word to the wise, you can’t use a numeric variable inside a
MsgBox by itself. If you attempt it, Excel will return a
#Value! error in the cell where you call the UDF. You must include a numeric variable as an argument (also known as a call parameter) to the
CStr() function. An example is:
Cstr(myVariable). You call this sample function by entering:
When you pass a date (actually an integer in the spreadsheet), the function handles it as a string. While no data type is assigned the
dateIn variable in the example, variables without an explicit data type are always of the
Variant data type. The
Variant data type is a master data type and can hold any other type, which makes it like the
Object data type in Java.
This program parses the string, then uses the
DateSerial() function to return it as a number. It’s critical to note that the last line returns the value in the
newSerialDate variable, and that variable must always be the function name. Place a single quote mark before all
MsgBox() function calls after verifying that the function works.
Public Function newSerialDate(dateIn) ' Define local variables. Dim day As String Dim month As String Dim year As String Dim startPosition As Integer Dim endPosition As Integer Dim length As Integer ' Initialize local variables and parse the month from the left. startPosition = 1 endPosition = InStr(startPosition, dateIn, "/") month = Left(dateIn, endPosition - 1) ' This is a debugging tool to track variables during execution. MsgBox ("Month [" + month + "]") ' Shift the start position and parse the day from the middle. startPosition = endPosition + 1 endPosition = InStr(startPosition, dateIn, "/") day = Mid(dateIn, startPosition, endPosition - startPosition) ' This is a debugging tool to track variables during execution. MsgBox ("Day [" + day + "]") ' Get the remainder of the string. startPosition = endPosition length = Len(dateIn) - startPosition year = Right(dateIn, length) ' This is a debugging tool to track variables during execution. MsgBox ("Year [" + year + "]") ' Return a value from a User Defined Function (UDF) by using ' the function name as the return variable. newSerialDate = DateSerial(year, month, day) End Function
Aside from the fact that all this parsing isn’t really necessary because the problem is much simpler and cleaner. At least, it becomes so when you understand the breadth of built-in functions in VBA. You can solve the problem by designating the formal parameter as a
Double like the example below.
Function newSerialDate(dateIn As Double) newSerialDate = dateIn End Function
Alternatively, you can accept a
Variant, which maps to a
String. Then, you convert it to a
Date like this:
Function newSerialDate(dateIn) newSerialDate = DateValue(dateIn) End Function
- Create the following spreadsheet, the formula values are noted below the screen shot. You should be able to copy and past them into the spreadsheet. After you’ve created the spreadsheet, entering a new date in cell
A1causes the UDF to run. When the UDF runs “as-is”, you’ll see message dialog boxes that show values set during runtime.
- You can now save this as an Add In library but comment out those debug
MsgBox()function calls. Click the Office Button and click SaveAs in the menu, then accept Excel Workbook initially. When you get to the SaveAs dialog, choose Excel Add-In as the file type. Below is a screen capture of the drop down selection box.
- Open a new Excel Workbook. Click the Excel Options button. Click the Add-Ins tab. Inside the right side pane, make sure the Manage drop down says Excel Add-ins before you click the Go button.
- Check the Samplefunction check box as shown below. Samplefunction is the name of the Workbook that contains the module, and it is saved as an
*.xlamfile. Click the OK button to add the library. You’ve now created, and added an Add-In library to your new spreadsheet. It will create a
SampleFunction.xlamfile in the users directory.
The file is found by default in:
Ranjit asked how you could call a UDF from inside a module. In the answer noted below, I show how to do it with the
Function Unary(number As Integer) Unary = number + 1 End Function
Then, enter the following ordinary
Sub Increment() ActiveCell.FormulaR1C1 = Unary(ActiveCell.Value()) End Sub
Enter a number in a cell, then navigate to Tools -> Macro -> Macros… and choose the
increment macro, which increments the value previously in the cell by calling the
As always, I hope this is helpful to a few folks.
This is probably defensive because I’ve had to answer the question about two dozen times since the iPad product announcement. The question is naturally, what do yo think about the iPad?
My perspective is biased by the fact that I’ve been using both DOS/Windows PCs and Macs since the 1980s. They each have merits but in short, unlike the media, I have a bias toward Apple products. In fact, I’m an old NeXT system administrator (software gone from the scene because as rumors have it, Steve wouldn’t think of letting the company become ONLY a software company).
I think the idea of the iPad for eBooks is awesome, the features are terrific. It clearly is a better opportunity for my digital movies but a bit awkward because of its size.
I can’t travel with an iPad by itself because it doesn’t support Microsoft Excel, Word, or Visio. That means I’d have to have my MacBook Pro and iPad. Ouch, the security folks will go nuts at the airport, and my bags are now heavier by about 2 pounds. The iPad is 1.5 pounds but the charger has weight too.
I understand all the logic for the device but there’s an underlying assumption in placing everything on the web. Some data can’t be on the web because of legal limits. This goes to my sticking point. Apple’s Office Suite isn’t as robust as Microsoft’s Office Suite. Keynote is easier to use and easily preferred over PowerPoint, but Numbers isn’t even close to Excel (here Apple fails). The problem with Pages is that many companies have templates built around Word and there’s no easy migration back and forth.
Perhaps Apple will reach out to Power Excel users and invest in Numbers to bring it into this millennium; and maybe they’ll also fix the portability between Word and Pages too. For example, one company I work with insists that I use Word 2003 because they’ve never updated their templates to Word 2007 (easy to do through VMWare Fusion). Then, all that’s needed is a rock solid replacement for Visio on Mac OS X.
I think that I might buy one to experiment with, just so I’m current with the product and new features. I’ve also got some product ideas that I’d like to explore but I don’t think this is a home-run like the iPod and iPhone without vitualization software to enable Windows. As an afterthought, maybe the announcement this summer will be “you can have it all now” when they port most features to the core OS X operating system. That would induce me to upgrade my MacBook Pro, wouldn’t it get you to do so too?
While reconfiguring my iMac, it was interesting to note the bumpy road to implementing Code Weaver’s CrossOver Plus 8.0.1 for the whole Microsoft Office Suite. Previously, it had only been used to support Microsoft Visio 2007.
I discovered that a Shutdown and Start operation was required after installing CrossOver Plus because a Restart hadn’t worked. Attempting to create the WINE bottle with a reboot gave me the following error:
The Microsoft Office 2007 package requires CrossOver HTML engine to install and run properly. Continuing with the install may produce unpredictable results.
I also found that patching the Office product failed. It’s possible that could be the network here, but there’s also no manual way to download *.msi files and apply them against the WINE bottle. It’s possible that a trick was missed and there is a way to do it. Downloading and right clicking on the *.msi file on the Mac OS X side allows you to look for an application to help but I couldn’t find a helper application in the CrossOver Plus folder tree. It may be there, however, where it is eluded me in the time my patience would allow (please comment if you know its location)?
Rather than use Google Quick Search Box I thought symbolic links would be helpful in the Applications folder since I actually also run Microsoft Office 2008. Turning to Chapter 6 in the product documentation didn’t solve the problem. It led me to an incorrect folder location.
They’re not here, as stated in the product documentation:
You can find them in the WINE bottle folders here, the default is for Windows XP:
Within the Bottle folder, you can find the Microsoft Office 2007 links here:
The CrossOver Plus installation suppresses the options dialog to choose a typical, full, or custom installation of Microsoft Office 2007. That means you get typical, which means you can’t connect to a remote database using the JDBC et cetera. This doesn’t meet my needs and means I’ll have to put another VMWare instance together to work with Microsoft Excel 2007 reasonably. Drat!
Holiday Gift from Microsoft
Windows 7 ships. Then, we find it’s really Windows Vista+ (code base 6.1). Now, Microsoft give us a late year present, the Microsoft Black Screen of Death (their original post has been wiped by agreement with Microsoft it appears but only the shadow knows and rumors on the web). What better excuse to rush out and buy a MacBook Pro or use this fix?
Holiday Gift from Apple
Buying a MacBook Pro makes sense if you don’t already have one. Owning one, I’m hesitant to upgrade my MacBook Pro because the battery in the new one requires a service call when the battery wears out. An expensive item because the battery life is generally poor after 1,000 recharge cycles and that number of recharges may or may not occur before your Applecare service contract runs out.
Battery life/replacement is one of the reasons why I’ve stayed on my old MacBook Pro (purchased 16 months ago). The other reason is that I plan on getting by with a MacBook Pro for 4 to 5 years not Apple’s apparent plannned obsolescence of 3 years. While I’m in the gripe mode, the new Apple Cinema Display is attractive but not compatible with older MacBook Pro or MacBook computers. Also, the Altona DVI to Mini Display Port has mixed reviews out there and Apple seems disinterested in helping owners of older machines use the new Cinema screens. There aren’t any other alternatives to the Altona product (at least that I’ve found). I almost feel that somebody at Apple watched the movie Robots too often because it seems my 16 month old MacBook Pro is an outmode and there isn’t an upgrade option (only a new purchase).
Some tips and techniques for Word seems like a good add to the blog. At least, those changes that I run into while writing for McGraw-Hill on my new book projects. This shows you how to enable Microsoft Word 2007 and 2008 readability statistics. Yes, it’s moved in the new release.
Microsoft Word 2007 has the Office Button. The Office Button holds the key to what were once tool options. You click on the Office Button in the upper left hand corner. You’ll see the following options dialog. Click the Word Options button to change options.
Choose the Proofing option in the left column, then check the Show readability statistics check box, as shown. You click the OK button when your done setting Word options.
Microsoft Word 2008 works like Mac OS X applications. You navigate to the Word on the menu and choose Preferences. That launches the following menu set.
Double click the Spelling and Grammer icon, which launches the Spelling and Grammar options dialog. Check the Show readability statistics box and then click the OK button to save the setting.