Monday, October 31, 2011

Review - Dell Inspiron 14z

Quick review: Dell Inspirion N411z "14z" laptop


The family needed a new laptop and after some pondering, we decided on the Dell Inspiron N411z, which is a fairly traditional laptop that is a little thinner and lighter than most. We were after a laptop larger than our Netbook and smaller than a 17" machine. 

Initial reactions:

Opening the box, we both said "wow, look at that."  This is an attractive machine that feels well-built and solid.  The top-surfaces are brushed aluminum and unlike other recent Dell laptops, this does not feel plastic-y.  The outside edges are sculpted and the screen is thin.

We like the computer and have found no significant problems. 
2012.06 Update: After 8 months, this computer still performs perfectly with no issues.


We ordered a standard, low-end model, with an i3 CPU, 500GB Disk and 4G RAM, all fairly pedestrian.  Do not confuse this computer with the high-end Dell XPS 14z.

It performs as expected and intended.  Windows Experience Index shows the following.  These are good performance specs, but all i3 machines should behave similarly.  In today's market, there is little performance variation from one brand to the next:



Detailed Reactions:

Your first reaction is the metal finish.  Compared to other less-expensive Dell computers, this machine feels like a real computer and not a disposable plastic toy.  Case-in-point: Earlier this month, we bought a 17" Dell Inspiron and hated the shiny-plastic lid and palm area -- every fingerprint showed as a greasy smudge.  This computer is different.  The metal feels cool to the touch and fingerprints won't be as much of a problem.

The 14z comes in two colors -- A dark, Espresso Brown (standard) and a deep burgundy Red ($20), both illustrated below.  Colors are deep and rich, along with a slight metal texture.  It would have been nice to see a brushed aluminum and white, harkening back to their popular M1530 laptops from 3 years ago.

The second thing you notice is the screen.  The LCD is backlit with LEDs, making the screen thinner than older styled laptops.  The hinge is offset about 20mm from the back edge, probably to help protect it, and the screen folds neatly into a recess on the top.  We liked the design. Colors are vibrant, with good viewing angles.

With the lid closed, the computer is about 10mm (3/8") thinner than most other laptops.  However, the 'thin-ness' is no where near as surprising as a Mac Air-Book or a Samsung Series 9.  But unlike an ultra-thin, this has all memory, external connections and a DVD that you would want. But still, it never hurts to be thin.

Sitting on a desk, the total thickness is taller than advertized (with the 6-Cell battery) because the curve of the battery-pack extends downward, acting as footpad and providing air circulation for the bottom panel.  In practice, you can ignore this, appreciating the thinness of the machine in other places.

Keyboard and TrackPad

The keyboard is the new style (chicklet) keyboard that all new laptops seem to have.  The keys have a good, solid feel, with good vertical travel but I wish they had more of a sculpted, bowl-curve on the top surface.  There is a top row of Fkeys, which have been missing on other laptops I have seen.

Click for a larger view; click "X" to return.
The color is more of a burgandy than red.  I need to learn how to white-balance my camera...


We paid an extra $30 for the backlit keyboard.  The outside edge of each key, as well as the printed letters, are illuminated.  In a darkened room, this is a nice feature.  It turns off after a few minutes of inactivity and I doubt it draws very much power.  The backlighting is more subtle than the photo indicates.



The trackpad is molded into the palm rest and is large, but not too large.  The Dell-supplied drivers are simple and functional and although I've not used it yet, the trackpad supports multiple-finger gestures.  For example, dragging two fingers down acts as a scroll.  You can also scroll using the Right and Bottom edges of the pad. 

The trackpad has one problem that other reviewers have written about: The buttons are stiff.  I agree.  It will not improve with age.  However, for normal clicking, you don't need the buttons; just tap on the pad.   

Side Ports

The left and right sides have various USB, and mini-display ports.  The back has an RJ45 and the power-jack.  Except for the power-jack, all ports are covered with a pliable molded plastic door that even if twisted, will not break.  Because of the inward curve of the case, Dell had to do something to cover the ports (the curve indents the ports by several millimeters).  The doors were a solution that gives the machine a smoother, more polished look.  But they have a problem in the 'Form follows function' arena.


The doors are inconvenient to open and you will need fingernails or a small blade. Small plastic tabs latch the doors shut and will undoubtedly wear after repeated use.  Unfortunately, the headphone jack is also behind one a door and you will have to open it each time headphones are used.  


Other side, showing door closed.  This is the Espresso Brown color.  Better than boring black.

External mice with Nano receivers will not be happy because the doors will not close.  I would suppose if you were industrious, you could cut a hole in the door.  Don't you wish all laptops had built-in nano receivers?

In any case, if you are always using external ports, the doors may be more trouble than they are worth.  When that day comes, I will be tempted to cut them off.  In Dell's defense, the doors look nice. 

Other Hardware Notes:

Unlike the higher-end laptops, this machine features a standard laptop DVD drive with a tray (not a slot-load).  I had minor difficulties closing the door, where the trim appeared to catch on the side of the case.  This is not a major concern, but I would use care when closing the tray. The CD/DVD drive is user-replaceable via a screw under the bottom panel.


The hard drive is not user-replaceable and requires an entire laptop dis-assembly to get to the component. 

The PowerBrick is the same used by other Dell laptops and appears to be interchangeable.  All of my recent Dell powerbricks work on this laptop.

The power-plug sticks directly out the back of the machine and because of this, it is susceptible to damage if the laptop is tipped backwards or if someone trips over the cable.  This is a design weakness in most laptops, including this machine.  The motherboard's power-jack is easily damaged and I can prove this with an older Dell laptop -- and yet, they continue to design them this way.  However, there is one bright spot:  The mother-board AC adapter plug is replaceable, without soldering -- but this is not a user-replaceable part.  See the Technical manual at the end of this article for details.



Technical Details, from Dell's site:

i3 or optional i5 Processor
4GB memory, standard, upgradeable to 8
14" High Definition LED 1366x768 with "True Life"; no other screen options
SATA harddrives, starting at 500GB

10/100 RJ45 Wired Network Connection
Wireless: Intel® Centrino® Wireless-N + WiMAX 6150 (1x2 bgn + WiMAX)
(Supports Wireless Mini Cards, should a replacement be needed)
Bluetooth: Intel® Centrino® Wireless-N 1030 (1x2 bgn + Bluetooth)

SD, MS, MMC CardSlot
2 USB 3.0 slots
1 USB 2.0 Powered slot
Combination Headphone/Microphone Jack (unsure how the combo works)
SRS Sound, 2 speakers, underneath the chassis, well placed
HDMI v1.4
Mini Display Port (no VGA or Digital); adapter cable not included
1Mpx WebCam

The battery is a user-replaceable 6-cell Li-Ion 65WHr (std).  You can also buy an optional 4-cell battery -- which is what allows them to advertise the machine is one inch thick. 



Pre-Installed Software - Crapware

Over the years, Dell has ebbed and flowed with pre-installed software, affectionately called "crapware" (trial software, limited use software).  This machine leans a little more on the crapware side of the fence but admittedly, there is a fine-line between one person's crap and another's handy feature.

I spent about 2 hours removing software and cleaning up the machine to my preferences.  Here is a summary of what I removed, with more details below.  Granted, some were nothing more than a PDF-documentation file and others could be construed as useful.  Since I have high standards and low tolerance for advertisements, I found most of the installed software expendable.

For example, an icon-link to ebay was provided.  This clearly has nothing to do with a laptop and Dell must have gotten a kickback for installing it so prominently on the desktop.  Although this was not an egregious sin, it was a disservice to their customers.  

Removed Software:

Software that was clearly crapware is marked in my list with an an asterisk (*).
If it were more benign or had some usefulness, along with advertising, I marked as ( ).
Unmarked software could be taken one way or another.

Mcafee Security Center (*)
Accidental Service Agreement (*)
Banctec service Agreement (*)
BLIO - K-NFB eReader (*)
Complete Care Business Service Agreement (*)
Consumer In-Home Service Agreement (*)
Dell Home Systems Service Agreement (*)

Ebay (*)
Microsoft Office 2010, Office Starter with advertising (*)

Microsoft PlayReady PC Runtime x86  (DRM licensing - no benefit to you) (*)
Premium Service Agreement (*)
QualxServ Service Agreement (*)
Sync-up by Nero $25.00 (*)
Skype and Skype Toolbar (*)
TrustedID (ID Protection - $40 per year) (*)
WildTangent Games (*)
Zinio eReader (*)

Acrobat Air ( )
Bing bar ( )

Cozi Family Calendar, free ( )
Microsoft SQL Server 2005 Compact Edition ( )
Windows Live Essentials ( )
Microsoft Silverlight ( )
Dell Stage - Buttonbar ( )
Dell Music Stage ( )
Dell Video Stage  ( )

Dell Digital Delivery (optional)

Dell DataSafe Local Backup (perhaps.  2G free for 1 year)
Dell DataSafe Online (optional)
Dell 'Stage' (includes AccuWether desktop widget)

Service Agreement Clutter
Several of the install programs were nothing more than a license/pdf for various service contracts, which you likely don't have.  These were all documented if you bought the service and they certainly these do not need to occupy space in the control panel.

McAfee
Most longtime Keyliner readers know that I dislike McAfee and Symantec's virus suites, thinking they are almost a virus themselves.  I always de-install, and install Microsoft's MSE.  See this Keyliner article.  If you wanted, you could keep the McAfee product for a year, but even at that price, I'm not sure it is worth it. Some day I'll give them a chance again.

Microsoft Office 2010 Advertising
Microsoft Office 2010 is the free, advertising-supported version.  If you need Office, buy it, or install a competing product, such as Corel's Office Suite.  I would never use the version installed on the disk. If you are going to spend this much money on an office-suite, get the installation media.

Microsoft Live
If you are not using Microsoft Live, uninstall it and save CPU cycles and disk space.

Microsoft PlayReady DRM
Microsoft PlayReady is a Digital Rights Management program, which I recommend un-installing.  This software does nothing to benefit you. 

DataSafe
Dell's DataSafe is needed one-time to spin an emergency recovery disk, but then it could be un-installed.  The remaining features, such as 1 year of free online backup (2G) is really an advertisement for the service.  Some day, perhaps, Dell will offer the backup service for free, as a benefit to owning their machines.

Dell Digital Delivery
Dell Digital Delivery is needed if you bought downloadable software from Dell.  If you did not buy software, un-install.  If you later decide you needed it, you can re-download from Dell's site. 

Zenio and Blio eReaders
Of interest, the computer came with two different (competing!) eReaders, neither of which I have heard of.  Both have links to their sites where books and magazines are for sale.  The readers are different than the Nook or Kindle and they are supposed to give a magazine's glossy-look and feel, with all the layout.  But, in the end, these are really advertisements and again, Dell must have gotten a kickback.  Remove them.

WildTangent Games
Similarly, WildTangent Games, and Sync-Up are advertisements and should be deleted.  I keep wishing the WildTangent games were just the installed games; some of them looked fun to play, but it is not worth the hassle of trying to figure out when money was involved.  There is also ebay and Skype.  If you need these services, visit those sites and install what you need at that time. 

Dell Stage
Finally, there is a slick-looking toolbar called Dell Stage.  I am mixed about this.  On one hand, it added flair to the desktop.  But I had troubles configuring and extending it to other things.  Other users on the web have similarly mixed views, with many really liking the program and others not.  In the end, I thought it pointed to commercial sites and wasn't as refined as it could have been.  Perhaps this warrants a closer look.  I hope the software is in the customer's interest and not another commercial venture, but I couldn't tell. Since I had doubts, I uninstalled it.

Once the software was removed, I streamlined the start menu, which makes the machine a joy to use.  See this Keyliner article: Streamlining Start Menus.  And this article on Cleaning Startup Programs.  These steps should be done on all computers.

Final Conclusions:

This is a full-featured laptop that is a pleasure to hold and use with no significant problems.  As configured above, we spent $650 (2011.10.27) and the machine was delivered in 5 days, standard freight.

Compared to other brands, it might be about $50 more than it should have been (given the RAM and hard drive), but we liked the style, with the Red and backlit keyboard.  Because of previous Dell purchases and service, we stayed with the brand. We are now approaching a year with the machine and we are still pleased.


Related Articles:
Windows 7 Streamline Start Menus (Organizing the Start Menu)
Windows XP/Vista Streamline Start Menus
WordPerfect, a Quick Review
Dell XPS Slim Power 
Review: Dell Mini 10
N144z Technical and Service Manual It is nice Dell publishes this.

Friday, October 21, 2011

Excel Parse CityStateZip Functions

Excel How to:  Parse City-State-Zip fields with an Excel User-Defined functions (UDF). A single cell, such as "Boise, ID 83703" can be parsed into individual fields. A variety of city-state-zip formats, including Canadian, are supported.  These are really smart parsing routines. This article has been streamlined and re-published on this date, with newer versions of the Macro code.

Related Article:
Keyliner: CityStateZip DLL CSZ Class Library - a more sophisticated routine

Parsing city-state-zip fields is complicated because the data can be in a variety of different styles and formats. Consider these examples:

Boise ID 83703
Boise, ID 83703-1111
Boise, ID 83703 1111
Boise,ID 83703
Boise ID 837031111
Post Falls, ID 83703 (multiple-word city)
Post _ _ _Falls, ID _ _ _83703 (embedded, redundant spaces, punctuation)
Salt Lake City UT 12345
Vancouver BC V1B-101 (Canadian)
Vancouver Park BC V1B 101



The Method:
With the code referenced in this article, you can build your own Excel functions.  For example, you are probably familiar with the =Sum() function.  With Excel's macro language, you can write your own function, such as "=ReturnCityName()".

Even with no macro experience, can take the code from this article, add it to your spreadsheet, and use the functions, without having to write or debug the routines.  You should be able to download, apply, and use these routines in about 5 minutes.


Overview:

The macros were written as Excel UDF's (Excel User-defined functions), where you can create your own Excel keywords.  Once installed, the macros can be applied and used like any other function in Excel.  Today, I used these very routines to parse a 200,000 row spreadsheet.  All of this logic happens in under 500 lines of code.

Main Function Names
=ReturnCityName()
=ReturnState()
=ReturnZipCode() Returns entire zipcode, punctuated

=ReturnZipCode5()
=ReturnZipCode4()
=SuperTrim()  (Trims extra leading, trailing and internal spaces)
=ReturnLastWord()


In this illustration, note the single cell with a city-state-zip in cell A5. 
The formula in cell B5 demonstrates the "ReturnCityName".  Other cells show a parsed State and Zipcode.




The routines work by first identifying the zipcode, then backing up, looking for the StateCode. Anything before the State/Province Code is considered a City name. If a postalcode is not found, it looks for a state/province code. The code can return either the string-values or the numeric positions.


See this article for a tutorial on how to build and use User-Defined Excel Functions or you can follow these step-by-step instructions and use the code as-is. 

What it handles
These routines correctly process City-State-Zip (CSZ) strings with these general characteristics:
  • Any type of US or Canadian Postal Code
  • Including ZipCode-5 digits and Zip4
  • Zipcodes can be with or without punctuation
  • City-names can be one or more words
  • State-codes (ID, NY, BC, B.C., ONT), etc.
  • State Names (New York, North Dakota) are supported, if comma-delimited

What it does not handle
With multiple-word state names ('North Dakota', 'British Columbia'), you must have a comma after the city name.

Lincoln City, North Dakota 83700-1234

Single-word state-codes ('ND', 'BC', 'D.C.', 'Idaho', 'Missouri') are handled properly with or without a comma.

Canadian Postal Codes are a mystery to me and forgive me for not knowing them but I believe they are properly accounted for. The code can be punctuated with a hyphen or a space (K1A-1B2 or K01 1B2). The Province Name must follow the state-name rules described above (As an aside, the postal-code is identified by a numeric digit in the 2nd and 7th position, with a space or a hyphen in the middle. The last digit must be numeric in order to detect a US or Canadian postal code.


Installation and Use
Source-code can be downloaded from Keyliner's public GDrive site.  You are welcome to use this code in your personal or commercial projects.

1.  Download two files from this link:
(You do not need to register as a user to download.  Highlight the files, then click the 'Download' button on the top ribbon-bar)

Download Keyliner Excel Macros

Download two files; both are required:
A210_ParseCityStateZip.bas
A800_UtilStrings.bas


2. Save the downloaded files to any directory, such as

C:\Data\Source\CommonVB\A210_ParseCityStateZip.bas
C:\Data\Source\CommonVB\A800_UtilStrings.bas


3. Launch Excel and Enable Excel Macros:

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.  This is a one-time step for all macros.
  • Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
  • Click Trust Center, Trust Center Settings
  • Macro Settings
  • Enable All Macros
  • Older versions of Office have similar settings.

4.  Open the sheet where you need to parse city-state-zip
or open a blank worksheet to test the routines.  In the Excel sheet, import the VB code using these standard Excel steps:

a.  Click the "orb", Excel Options 
b.  On the left, choose "Popular"
c.  Click [x] Show Developer tab in the ribbon and return to the sheet


5.  Start the VBA Macro Editor by pressing Alt-F11.
The VBA Macro Editor will open in a new window.


6. On the tree-diagram, illustrated below, select your sheet ("Book1"), then:
  • Other-Mouse-Click (Book1),
  • Choose "Import File"
  • Browse and select the previously-saved ".bas" module,
    e.g. C:\Data\Source\CommonVB\A210_ParseCityStateZip.bas
  • You will see the code appear under the "Modules" folder (not illustrated)
  • Repeat this step and import
    C:\Data\Source\CommonVB\A800_UtilString.bas  (the second macro file)

7. Close or minimize the Visual Basic editor by clicking the editor's "X".  This returns you to the sheet and the macros are ready to use. 

Note: All the macro editor did was "attach" the code.  You can optionally choose to view the programming code by using the editor. 

Also, the macros will save with the sheet, but only if you follow the Office 2007/2010 Save warning, documented near the end of this article.



Testing:

A. In Cell A1, type an example City-State-Zipcode for testing. Press Enter when done:

example:  Boise, ID 83703-1234


B.  In cell B1, type this formula:

=ReturnZipCode(A1)

Results: The Zipcode should return.

Try these other functions: 
"=ReturnState()"
"=ReturnCityName()"
"=ReturnZipCode5()"

Possible Errors:
If you get a "#NAME?" when typing =ReturnZipCode, the library was not linked in properly or was not linked to this particular sheet (the macros are only available to the sheets where they were copied).  Review the steps above.  If a routine, such as "SuperTrim" or "FindLastWordPos" fails, confirm you linked in the A800_UtilStrings library.



Excel 2007/2010 Save Warning:

When Excel 2007/2010 saves the sheet, it will save it as an ".XLSX" and will strip the macros out as it saves! (Microsoft is doing this as a security precaution.)



When prompted: 'Do you want to save as a macro-safe) workbook', click "Yes"  or optionally, do a File-Save-As, and choose "Excel Macro-enabled Workbook (*.xlsm).


Descriptions
This article does not explain how this code was developed; that has been left for your study. But the provided code should work well with any version of Microsoft Excel, from version 97 onward. This code has been tested with Office 2007. Converting the code to VB6 or VB.Net should be nearly transparent. All numeric position/counters are base-1, per VisualBasic's norm.

Functions within the A800_UtilStrings routine are of particular interest:
=SuperTrim()
Trims all duplicate interior, leading and trailing spaces. This function is potentially helpful in a wide variety of situations, above and beyond CSZ parsing.

=FindLastSpacePos()
Returns the position of the last space, base-1; returns a numeric value

=ReturnFirstWord()
=ReturnLastWord()
Returns the first and last word in a string; properly trimmed.

=FindStatePos()
Returns the State-code/State-name's numeric position in the string; base-1. Other functions return similar values:

=ReturnCityName()
=ReturnState()
=ReturnZipCode()
=ReturnZipCode4 ()

=GetCSZPositions

Returns 4 numeric values, showing the position of each of the CSZ's subparts. See the comments near the top of the code for a complete description.

Other functions, such as =FindState, return the numeric positions and are provided as a convenience.

Comments welcome.

Link
Related articles:
Excel Macros - Formatting Phone Numbers
Excel Macros to Parse City State ZipCodes
Excel Macros to return First Word, Last Word, Supertrim, Count Words
Writing Excel UDF functions
Excel VLookup - A complete Tutorial
Excel Coloring Alternate Rows
Excel Importing Leading Zeroes

Thursday, October 20, 2011

Excel Code: VB ReturnFirst/Last and SuperTrim

Reference: These Excel/VB macros for ReturnFirstWord, ReturnLastWord and SuperTrim are very handy.  Download these routines, free of charge, without registration, from Keyliner's Public GDrive site.  Details on how to use these functions can be found in other Keyliner articles.  This article was streamlined and re-published.

Contents:
=ReturnFirstWord
=ReturnLastWord

=FindLastSpacePosition (numeric result)
=SuperTrim; Trims multiple redundant leading, trailing *and* interior spaces

=CountWords

These Excel UDF Macros (User Defined Functions) can be downloaded and attached to a speadsheet in a matter of minutes.  Once in place, they can be used in any Excel formula.  Other Keyliner routines, such as Excel CityStateZip Parse Functions use these modules.
 

Installation and Use

Source-code can be downloaded from the Keyliner public GDrive site  (formerly, this article listed the code, but the code became too ungainly to post in a blog).  You are welcome to use this code in your personal or commercial projects.

1.  Download Excel Macros from link:
(You do not need to register as a user to download)
Keyliner' Public GDrive


Download:
A800_UtilStrings.bas


2. Save the downloaded files to any directory, such as:
C:\Data\Source\CommonVB\A800_UtilStrings.bas


3. Launch Excel and Enable Excel Macros.

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.
  • Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
  • Click Trust Center, Trust Center Settings
  • Macro Settings
  • Enable All Macros
  • Older versions of Office have similar settings.

4.  Launch Excel to a blank sheet and import the VB code using these standard Excel steps:

Click the "orb", Excel Options. 

On the left, choose "Popular"
Click [x] Show Developer tab in the ribbon

5.  Press Alt-F11 (Starts the VBA / Macro Editor)

6. On the tree-diagram, illustrated below, select your sheet ("Book1"), then:
  • Other-Mouse-Click (Book1),
  • Choose "Import File"
  • Browse and select the previously-saved module, e.g. C:\Data\Source\CommonVB\A800_UtilStrings.bas
  • You will see the code appear under the "Modules" folder (not yet in the illustration below but will be when you complete these steps)
7. Close the Visual Basic editor and return the original sheet; clicking the big "X".




Testing:

A. In Cell A1, type an example string, with leading, trailing and extra internal spaces. Press Enter when done:

example: "  Now is    the time "


B.  In cell B1, type this formula:

=SuperTrim(A1)

Results:  "Now is the time"  (with interior spaces trimmed)

C.  In cell B2, type this formula:

=ReturnLastWord(A1)


Results:  "time"


Related Keyliner Articles:
Excel UDF (User Defined Functions)

Excel VLookup - a complete tutorial
Excel Coloring Alternate Rows
Excel Parsing City-State-Zip
Excel Importing Text with Leading Zeroes
Using VBA to Send Email

Wednesday, October 19, 2011

Salutation List Reference

Reference list for Salutations (English)

Prefixes, salutations or honorifics, such as "Dear Rt Hon Mr." John Q. Smith,

I had a request to parse prefix Salutations from a database of names and had troubles finding a comprehensive list.   Below is that list, along with links on how I did the parsing.

Shorter, common salutations are punctuated with and without periods while longer ones, by convention, are listed without punctuation (e.g. Rt Hon Mr  vs  Rt. Hon. Mr.). The referenced parsing routines use this list and the logic is easily changed for additional entries.

Leave an un-registered comment if you have additional salutations.  I purposely dropped the Pope and the Queen, figuring we would never need to write to them using a database and mail-merge.

Related articles:
Parsing First and Last Names in Excel
Parsing Salutation in Excel (Parses this list)

Parsing City State Zipcodes in Excel
Excel User-defined formulas

English Prefixed Salutations

Admiral
Air Cdre
Air Commodore
Air Marshal
Amb
Amb.
Ambassador
Baron
Brig
Brig Gen
Brig General
Brig.
Brigadier
Brigadier General
Bro
Bro.
Brother
Canon
Capt
Capt.
Captain
Cardinal
Cdr
Cdr.
Chief
Chief Warrant Officer
Cik
Cik.
Cmdr
Cmdr.
Col
Col.
Colonel
Commandant
Commander
Commissioner
Commodore
Congressman
Corporal
Cpl
Cpl.
Cpt.
Cpt
Dr
Dr.
Dr and Mrs
Dr. and Mrs.
Dr & Mrs
Dr & Mrs.
Father
First Lieutenant
First Officer
Flt Lieut
Fr.
Gen
Gen.
Generaal
General
Gov.
Governor
Group Captain
Grp Capt
Hon
Hon.
Honorable
Hr
Hr.
Judge
Justice
Lieut
Lieut Cdr
Lieut Col
Lieut Gen
Lieut.
Lieutenant Colonel
Lieutenant General
Lt Col
Lt Col.
Lt.
Maj Gen
Maj.
Major
Major General
Master Warrant Officer
Mayor
Miss
Mr
Mr.
Mrs
Mrs.
Ms
Ms.
Pr
Pr.
Pastor
President
Private
Prof
Prof Dr
Prof Dr.
Prof Sir
Prof.
Professor
Pvt
Pvt.
Rabbi
Rep.
Representative
Rev
Rev Canon
Rev Dr
Rev Mother
Rev.
Reverend
Rt Hon
Rt Hon Mr
Rt Hon Mr.
Rt Hon Ms
Rt Hon Ms.
Rt Hon Mrs
Rt Hon Mrs.
Rt Hon.
Rva
Sec.
Second Lieutenant
Secretary
Sen
Sen.
Senator
Sergeant
Sergeant
Sgt.
Sister
Sr
Sr D
Sr.
Sra
Srta
Sultan
Tan Sri
Tan Sri Dato
The Hon Dr
The Hon Dr.
The Hon Justice
The Hon Miss
The Hon Mr
The Hon Mr.
The Hon Mrs
The Hon Mrs.
The Hon Ms
The Hon Ms.
The Hon Sir
The Honorable
The Most Reverend
The Reverend
The Venerable
The Very Rev
The Very Rev.
The Very Reverend
Vice Admiral
Warrant Officer

Sunday, October 16, 2011

Excel - Macro Editor Removes Spaces

How To: The Excel 2007 Macro Editor is automatically removing spaces between words while typing code.

Problem: 
While typing a new function name or other text in the Excel Visual Basic Macro, the editor  is removing the spaces between words as you type (automatically, unexpectedly, auto removing, auto-correcting).  For example, you may be attempting to type a new function name:

Public Function MyFunction()
but the editor will correct the text as "PublicFunctionMyFunction()"



Note: You have also likely installed Microsoft Visual Studio 2010 sometime previously.  This product introduced a Load Test Report Add-in that is apparently causing this bug.  This article is current as-of Office 2007, SP2.  I suspect Office 2010 exhibits the same issue.


Solution:

1.   De-activate the Excel "Load Test Report Addin"  (add-in) using these steps:

a.  Click the Excel "orb" (File menu)
b.  Click the bottom-right button, "Excel Options"
c.  Click left-side "Add-Ins" menu
d.  In bottom, center pull-down, Choose "Manage" "COM Add-ins".  Click Go


2.  Uncheck [ ] "Load Test Report Addin"




3.  Press Alt-F11 to return to the Macro editor.  The problem should be resolved.

Making this change is permanent (for this workstation) and the change sticks for the current worksheet and all new and existing sheets.

Keywords:
VB VBA Editor auto-delete auto-deletes auto delete spaces at the end of the line, spaces as typing, spaces while typing, removes spaces, collapses spaces

Tuesday, October 11, 2011

Excel - Enable Macro Developer Options

How To:  Excel Developer Options - How to enable Macro and Macro Editing for Excel 2007/2010/2013.  This is an excerpt from a previous article

1. Launch Excel and Enable Excel Macros.

Microsoft considers all macros a security risk and this can be a nuisance.
Do the following to enable the macros.  This is a one-time step for all macros.

  • Launch Excel; click the Orb (formerly the File Menu); choose Excel Options
  • Click Trust Center, Trust Center Settings
  • Macro Settings
  • Enable All Macros
  • Older versions of Office have similar settings.

2.  Expose Developer Options

Office 2008
a.  Click the "orb" (or File Menu), Excel Options 
b.  On the left, choose "Popular"
c.  Click [x] Show Developer tab in the ribbon
d.  Click OK and return to the sheet

Office 2010
a.  Click File, Excel Options, "Customize Ribbon"
b.  In the second-column ("Customize Ribbon: Main Tabs"), check [x] Developer
c.  Click OK and return to the sheet


3.  Enable Option Explicit Variables
In Office 2010 and above:
From any Blank Sheet, press Alt-F11 to open the VBA Editor

a.   Select Menu "Tools", Options, [Editor Tab],
b.  Check [x] Require Variable Declaration

This replaces the "Option Explicit" line you used to have to type in macros -- forcing you to declare variables before using - a recommended practice.

Related Posts:
Parsing First and Last Names in Excel
Parsing City State Zip in Excel
SuperTrim in Excel
User Defined Functions in Excel