Friday, March 20, 2015

Disable Cursor and Menu Animations in Office 2013

Disable cursor and menu animations in Office 2013
Slow scrolling in Excel 2013
Slow cursor positioning in Excel 2013 (Office 2013)

Issue:
In Office 2013, when clicking cells, typing, scrolling, selecting menus, etc., especially in Excel and Word, it now uses animation to give the movement to the action.  For most of us, it "feels" intolerably slow.

Solution
:
Microsoft did not expose an option for disabling this feature.  Use the Registry Editor to correct.  If you have never worked in the Windows Registry, do not panic; the steps are easy.

1.  Run "RegEdit"  (the Windows Registry Editor)

  Steps:
- From the Windows 8 tile menu, click anywhere on the background
  or from the Windows 7 Start Menu, press and hold the Windows key-R (for "Run")

- Type the word "Regedit" (press Enter)  e.g. Start, Run "Regedit"


2.  On the left-side tree diagram, scroll to this location, expanding folders, as needed.

HKey_Current_User\Software\Microsoft\Office\15.0\Common

Steps:
a.  In the Registry Editor's left-nav, scroll down the tree, opening these folders by clicking the "triangle" next to each folder:
 
HKey_Current_User
Software
Microsoft
Office
15.0
Common

b.  Highlight (single-click) the "Common" folder.
"other-mouse-click" the highlighted Common folder, choose  "New, Key"

Click all images for a larger size
c.  In the new Key name field (see new folder on left-nav), type "Graphics"  (case-sensitive).  You are renaming the "New Key #1" field.  Press Enter to accept the new key-name.


3.  Look to the right side of the program.  In the newly-built Graphics folder, which is empty except for a "default" value, activate the right-side of the panel by clicking once on a blank area (the detail side of the panel).

- In the detail pane, "other-mouse-click" a blank area on the panel,
- Choose "New", "DWord (32-bit) Value"

- Name the new value "DisableAnimations" (case-sensitive, no spaces).  Press Enter



- When prompted, assign "DisableAnimations" a value of 1 (hex).  Click OK to save.
If you are not prompted, double-click the new DWord value:


4.  Close the Registry Editor.  Changes take effect immediately.

Notes:
This value is set per-user.  Multiple users (or roaming users), who login to the Windows Computer, will each need to make this change. This change will not work if applied to HKLM.


Here is the Registry Merge file, if you are so inclined:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Microsoft\Office\15.0\Common\Graphics]
"DisableAnimations"=dword:00000001


Many sites on the web recommend turning off all Windows (8) animation, using
My Computer, Desktop, Properties, Advanced System Settings, Performance, and disabling "Animate controls and elements inside Windows".  This would be a mistake, disabling all animation, everywhere in Windows.  If you just wanted to target office, this document is a better solution.

Related articles:
Stop Excel Active Hyperlinks
Excel User Defined Functions - very cool
Excel VLookups - Comprehensive How-to
Excel - Parse City-state-Zip




Thursday, March 5, 2015

CityStateZip DLL - Reliably Parse CSZ fields using this class library

CityStateZip DLL - Reliably parse CSZ fields using a Windows class library developed by Keyliner.com.  Without bragging, this routine is slick - it understands and knows all about city-state-zip fields.  It can figure this stuff out, no matter how badly typed.

You are welcome to use this library for commercial and non-commercial use.  This is a different (and more powerful) routine than the VBA programs published previously.



Related Article:
Parsing CityStateZip in Excel using VBA





Description:

I periodically need to parse a single field's City-State-Zip into its constituent parts, typically after processing US Postal files from a program called "NCOA".

Example Standard City-State-Zip:
Salt Lake City UTAH 84101 1234

Returned Constituent parts:
City:    Salt Lake City
State:   UT

ZipCode: 84101-1234
Zip5:    84101

Zip4:    1234

or, it can return a fully-assembled and re-punctuated CSZ:
Salt Lake City, UT 84101-1234

The routine takes a passed City-State-Zip and can return either a newly-re-assembled string, with proper punctuation and state codes -- or it can return each constituent part, complete with two-character State and Province Codes.

It can handle multi-word city, multi-word states, with or without punctuation.  It converts abbreviated or spelled-out state names, converting them to US Postal 2-character codes.  It knows all about zipcodes and country codes.

Badly-formed CSZ's, such as these, are correctly parsed:

Results:
In each case, the city, official State-code, and a properly-formatted zipcode is returned.  You can return individual components, such as the city, or a fully-formed, correctly punctuated string.  This can handle Canadian Provinces, US Territories, and US Military Destinations, such as Europe ("AE").  It can handle Country Names (e.g. Vancouver B.C. 1A2-1B3 Canada), etc.


The routines prefer to work, but is not required, with a comma after the city name.

This is a Windows class library and is expected to be linked into your program's (C# or other) code as a new module.  Once linked, the following public methods are available.  See below for instructions on how to link.

Public Methods:
ReturnFormattedCityStateZip
ReturnStateName
ReturnCityName
ReturnZipCode (full postal-code, zip5 + zip4)
ReturnZip5
ReturnZip4
ReturnCountryName (Canada, etc.)

Other Public Methods (utility routines)
ReturnStateAbbr (Returns "WV" for "West Virginia")
SuperTrim (trims redundant internal spaces, hyphen-spaces, etc.)
CountWords
ReturnFirstWord
ReturnLastWord
FindLastSpacePos (position of last-word)

Complete Documentation on these Methods can be found here:
Documentation - CSZ Public Methods


Download Instructions:

Download the DLL/Class library from keyliner's G-Drive:
G-Drive Link: ns801_CityState.dll


Download a sample program (executable - dot-net 4.5) to test or try out the library.
This executable is self-contained and does not require the DLL.

Example Program download:
G-Drive Link: CityStateZipTest.exe

Self-contained test program, available for download
The Test program has the DLL linked internally, and the external DLL is not needed to run the program.  This allows you to manually type CSZ's to see how they will parse.  This is not meant, nor is it usable for production work.  You will need to apply the DLL to your specific project, with the instructions below.


Legal stuff: There is no charge or registration required to download these routines.  You are free to use for commercial and non-commercial use.  If you use the routines, leave a comment, telling me how it went.  Please give credit to the author in your commercial projects.  You may not re-distribute the DLL as a stand-alone distribution; instead, link to this article.  Much effort has gone into the development, but the author assumes no liability for errors or mistakes.  Test before deploying in a production environment.  If you find errors or mis-parsed lines, I would like to know about them.


Linking and Use Instructions

This is a standard, unsigned DLL, which you can link into your development project as a resource.  Once linked and instantiated, all public methods are exposed and can be used in your program

These instructions assume a Visual Studio C# project.  Your development language or environment may be different, but the steps are similar:

A.  Save the DLL (ns801_CityState.dll) to a known location.

For example, C:\Data\Source\CommonVS\ns801_CityState.DLL


B.  From your project (development code), open Solution Explorer.  "Other-mouse-click" References, choosing "Add Reference".


C.  Browse to the DLL's location  (e.g. C:\Data\Source\CommonVS\ns801_CityState.DLL)


D.  At the top of your program, add this recommended 'using' statement:

using ns801_CityState;


E.  In the public partial class Form1 (or other location, declare a class or method variable)

//Assigning a name "citystate." to the module
//Note: No equalsign, no parens: 
cl801_CityState citystate;


F.  Typically in the Form1's constructor, after "InitializeComponent," -- or in a function or method of your choosing, instantiate the class with this statement:

citystate = new cl801_CityState();


G.  The class is ready to use:  For example:
citystate.ReturnCityName ("Boise Idaho 83700 1234");

Click for larger view

Pending:
Can the DLL be linked into an Excel Spreadsheet or VBA routine?  I'll fiddle with the idea in the future.


A boat-load of work went into building this class library.
If you find it useful, make a donation to the International Keyliner Pizza Fund (via PayPal).
It would make me happy and you proud:


If you have a suggestion, feature request, or find something it does not parse correctly, leave a comment, below.  Registration not required.

I wonder: Should this routine return a "Proper"-cased string?  Currently, it returns the data, as typed.  Comments on this are welcome.

Version History:
1.01  Initial Release

Related Articles:

Similar, but less powerful routines were written for Excel, and they can be found here:
Parsing CityStateZip in Excel using VBA

Related Items:

DLL/Class library Download from keyliner's G-Drive:  ns801_CityState.dll
Example Program download: CityStateZipTest.exe
Documentation - CSZ Public Methods

Wednesday, March 4, 2015

Documentation - CityStateZip Public Functions

The Keyliner class Library "cl801_CityState" will parse any City-State-Zip field and return its constituent parts.  This post describes the public methods and is meant for reference

See this article for a complete description of the class library, with download instructions
Keyliner: City-State-Zip DLL - Reliably Parse CSZ Fields


Example Standard City-State-Zip:
Salt Lake City UTAH 84101 1234

Returned Constituent parts:
City:    Salt Lake City
State:   UT

ZipCode: 84101-1234
Zip5:    84101

Zip4:    1234

or Return a fully-assembled and re-punctuated CSZ:
Salt Lake City, UT 84101-1234


For documentation, these are the public methods in the class library.

Class Library (DLL) "cl801_CityState"


Public Methods


About
public string ns801CityState_About()
---------------------------------------------------------------------------------------------------------------
Returns a string pointing to this article.



ReturnFormattedCityStateZip
public string ReturnFormattedCityStateZip (string strpassedString) 
---------------------------------------------------------------------------------------------------------------
This returns a fully formed CSZ string, such as "Boise, ID 83700-1234", punctuated and supertrimmed.  


Pass any CSZ field, formatted in any fashion, to this method
Returned is a single string: "City, State-Code Zip5-Zip4" 

States are resolved to USPostal two-character State-Codes, when possible
Properly punctuated, fully assembled, in the same upper-lower-case (case) as sent
Military, Canadian, and US Territory codes are supported

For example:
"Boise,   Idaho     83700 - 1234"  -- returns "Boise, ID 83700-1234"
"Boise ID 83700" -- returns "Boise, ID 83700"
"Miles City 83700" -- returns "Miles City 83700"  (note no comma)
",Utah 83700" -- returns "*, UT 83700"  (note the asterisk)

Do not use this method if you want to individual fields returned;  Instead, call the individual field methods, below.

This method is relatively inefficient because it calls each of the field routines below, and then re-assembles the results, making numerous calls the same parsing routines.




ReturnStateName
public string ReturnStateName (string strpassedString)
---------------------------------------------------------------------------------------------------------------
This returns the State-Code (State Name), Province, or US Territory Code, favoring US Postal recommendations when possible, but falling back to the as-typed version if it cannot be resolved.

For example,
"Id.", "ID", "Idaho", "Ida." -- all return "ID"
"N.Y.", "ny", "NewYork", "New York" -- all return "NY"
"S.C.", "S. Carolina", "S.Carolina", "South Carolina" -- all return "SC"

Canadian Province, U.S. Territories, and Military codes are supported.

Pass a standard CSZ string (e.g.  "Salt Lake City, Utah 84101"), with or without punctuation, returns "UT".

If the State-code cannot be reconciled against the US Postal recommendations, the state is returned, as-typed.  For example, "Boise, zIdaho" returns a state-value of "zIadaho", as typed.   The comma helps the decision.

Without a city-comma, the routine will return correctly-spelled State names or State-Codes and abbreviations into the STATE field; other wise, they are appended to the City Name, making the address likely still deliverable.  For example:

Boise Idaho 83700  -- returns State Code "ID"
Boise Zidaho 83700  -- returns State Code "" (empty), with city "Boise Zidaho"



ReturnCityName
public string ReturnCityName (string strpassedString)
---------------------------------------------------------------------------------------------------------------
Returns the City name from any CSZ string, with or without a city-comma.

A comma is not required to parse the city name, but in some instances, a comma will return better results, especially if the State-name is non-standard or non-determinable.

Multiple-word City names are supported:

"Post Falls, ID 83700"  -- returns "Post Falls"
"Post Falls ID  83700"   -- returns "Post Falls"
"Salt Lake City Utah"  -- returns "Salt Lake City"

The returned City name is in the same case (upper/lower) as-typed.
Your calling routine should upper-case or proper-case as needed*

Missing City names, such as a badly-formed CSV file, behave differently, depending on the city-comma.  For example, consider this string, with a leading comma:

",ID 83700"  --returns CityName = "*" (an asterisk)

indicating a required city is missing and this will not be deliverable by the post office.  The asterisk helps identify bad records.  Zipcodes can span multiple cities and in most cases, a city is required.  (However, with a zipcode, the State is optional, see below).

For example, badly-formed records, without a city-comma, will process properly and will result in a deliverable mail, albeit it won't be liked by the Post Office:

"Boise 83700" or
"Boise, 83700"
-- returns CityName = "Boise";  State-Code = (empty). 



ReturnZipCode
public string ReturnZipCode (string strpassedString)

---------------------------------------------------------------------------------------------------------------
Returns the complete Zip or Postal-Code, including Zip-5+Zip4, as one string.  As with most public methods in this class, pass a fully-formed CSZ as the passed-string; returned results will be the ZipCode, fully-formed and punctuated.

For example, passing
"Boise, ID 83703-1234"  -- returns "83703-1234"
"Boise, ID 83703 1234"  -- returns "83703-1234"
"Boise, ID 837031234"  -- returns "83703-1234"
"Boise, ID 83703"  -- returns "83703"
"Belleview ONT K01 1B2"  -- returns "K01-1B2"  (Canadian)

"Amherst MA 1003" -- returns "01003" - leading zero was stripped, typically by Excel
"Baton Rouge, MI" -- returns "" (empty-string - missing postal code)

Note: The logic does not confirm the ZipCode against the State, nor is the zipcode cross-checked for accuracy.



ReturnZip5
public string ReturnZip5 (string strpassedString)
---------------------------------------------------------------------------------------------------------------
Returns the Zip5-part of the ZipCode, stripping Zip4.  Pass a fully-formed CSZ (City-State-Zip) into the method and get returned the Zip5.

For example, passing
"Boise, ID 83703-1234" -- returns "83703"
"Boise ID 83703 1234"  -- returns "83703"
"Boise Idaho 837031234"  -- returns "83703"
"Boise, ID 83703"  -- returns "83703"
"Boise, ID"  -- returns "" (empty string)
"Belleview ONT K01 1B2"  -- returns "K01-1B2"  (Canadian)
"Belleview ON K011b2"  -- returns "K01-1B2"



ReturnZip4
public string ReturnZip4 (string strpassedString)
---------------------------------------------------------------------------------------------------------------
Returns only the Zip4-part of the ZipCode, if present.  Pass a fully-formed CSZ (City-State-Zip) into the method and get returned the Zip5.

For example, passing
"Boise, ID 83703-1234"  -- returns "1234"

"Boise Idaho 837031234"  -- returns "1234"
"Boise, ID 83703"  -- returns "" (empty string - no zip4 information)
"Boise, ID"  -- returns "" (empty string, no zipcode passed)
"Belleview ONT K01 1B2"  (Canadian)  -- returns "" (empty string)



ReturnStateAbbr
public string ReturnStateAbbr (string strpassedString
---------------------------------------------------------------------------------------------------------------
When passed a State Name or abbreviation, returns the US Postal recommended two-character State-Code, Province, or US. Territory.  This routine is called by all other public State methods and you do not need to call this routine directly.  It is exposed as a public method, should you find other needs. 

For example, passing
"Id"  -- returns "ID"
"District of Columbia"  -- returns "DC"
"W.V."  -- returns "WV"
"W.Virginia"  -- returns "WV"
"West Virginia"  -- returns "WV"
"W Virginia"  -- returns "WV"

If a State (province, etc.) cannot be resolved, the original string is returned, unmolested.  Common variants are supported (N.Y., NewYork, etc.).  Advise the author if you find any missing.

State-Codes can be parsed, with or without a comma (the city-comma), but work best when a comma is present.  See "ReturnCityName" for more information on comma-processing



ReturnCountryName
public string ReturnCountryName (string strpassedString
---------------------------------------------------------------------------------------------------------------
Returns the Country name, if typed on the CSZ line (be aware most people address the Country name on a separate envelope-line and it would not be seen or processed by this routine).

For example:
Boise, ID 83700-1234 U.S.  -- returns "U.S."
Belleview ON LDU-9Z1 Canada  -- returns "Canada"

This routine is relatively stupid.  Basically, everything to the right of the Zipcode is considered a Country name.  No effort is made to validate as this is just a parsing routine. 



Other Public Methods included in this Library:


SuperTrim
public string SuperTrim(string strpassedString)
---------------------------------------------------------------------------------------------------------------
Trims multiple leading, trailing and redundant interior spaces from a string.
Space-hyphne-Space (" - ") are compressed into a single hyphen.
Leading commas and hyphens are stripped (orphaned commas, orphaned hyphens).
Trailing hyphens are stripped.
EM and EMM dashes (long dashes) are replaced with simple hyphens.

For example:
"   The     rain  falls.  "  -- returns "The rain falls."
"Boise  , ID 83700 - 1234"  -- returns "Boise, ID 83700-1234"
",ID 83700"  -- returns "ID 83700"



CountWords
public int CountWords(string strpassedString)
---------------------------------------------------------------------------------------------------------------
Returns a numeric count showing how many words are in the string, where words are delimited with a space or hyphen.  Words are super-trimmed prior to counting -- multiple embedded spaces are compressed.  For example:

"Three   dog   night"  -- returns 3
"My three sons-and a daughter"  -- returns 6



ReturnFirstWord
ReturnLastWord

public string ReturnFirstWord(string strpassedString)
public string ReturnLastWord(string strpassedString)
---------------------------------------------------------------------------------------------------------------
Returns the first word or last word in a string.  If only one word passed, that word is returned.  The string is simple-trimmed before processing.  Hyphenated words and other punctuation are not considered in these routines.



FindLastSpacePos
public int FindLastSpacePos(string strpassedString)
---------------------------------------------------------------------------------------------------------------
Returns a numeric, base-zero count showing the last space (character) found in the string - Horizontal Position.  This is a simple ".LastIndexOf(' ')" and does not account for multiple interior spaces.  For best results, supertrim prior to calling.



TruncateTrailingPunctuation
public string TruncateTrailingPunctuation(string strpassedString)
---------------------------------------------------------------------------------------------------------------
Removes all manner of trailing punctuation on a string, including commas, periods, special characters. This uses a .TrimEnd(array) method.

See this article for a complete description of the class library, with download instructions
Keyliner: City-State-Zip DLL - Reliably Parse CSZ Fields