DataTemplates in Silverlight

DataTemplates specifies visualisation of data objects. Some functionality of this was lost in Silverlight 4.

The Business of Overdrive: DRM Entrepreneurs

Overdrive: the Rights Management entrepreneurs, provide software to process digitally protected audio-books but also provide a distribution service, where titles are warehoused on behalf of publishers. It's run by founder Steve Potash.

Programming Zip in C#: Long Live Gzip!

GzipStream
DeflateStream

Not sufficient for files in folder structures, hence

SharpZipLib

Zip Primer - gzip data format is the GNU format for lossless file compression and decompression. LOSSLESS is industry-jargon for being able to restore the original file/image exactly as before. LOSSY compression (allowing for APPROXIMATE restoral) is interesting because it has better compression rates.

Configuration Progress Screen When Opening Word

Can happen when multiple versions of Word are installed on the same machine. There are some rules when doing installations: e.g. earliest first.
According to KB928091, the Windows Installer runs whenever the version of Word that you start is not the one that is registered.

POVRay for Windows

12MB MSI (32-bit PoVRay). The last official version was released June 2009. It was started by a guy writing stuff for the Amiga. It evolved from a creation called DKBTrace.

All about XAML

UI VERSUS LOGIC

XAML is a markup language -> separate UI and logic. Have a .xaml extension. Typically files are encoded as UTF-8 (8-bit Unicode Transformation Format). Example of creating a button:

<StackPanel>
<Button Content="Click Me"/>
</StackPanel>

A StackPanel allows you to stack elements in a specified direction. With a StackPanel, content can flow vertically (the default) or horizontally.

Microsoft Research Projects

Stay up-to-date with the latest output from MS Research.

Try Infer.Net from MS Research.

To see some of the project output you need Silverlight (6MB exe file, currently on v4).

Learning a New Software Product

Learning a new software product is fraught with pain and anguish.

Error messages systematically archived to a notepad file help to alleviate diagnostics but what about screenshots? In fact, WordPad is not bad in this regard.

Relative tradeoffs between WordPad vs Ms WORD make wordpad the preferred tool in many instances.

Another Egregious Parsing Bug in Microsoft Word (and Excel)

Another nasty parsing bug in Word. Wasted 2 hours of my time. Fully replicable. Where is the quality control? Excel also - tries to recognise dates in text when no dates exist. Special characters like left-arrow and dash always confuse Word and Excel. Both applications start to look for patterns that are not present. Madness!! Parsing errors are the most problematic aspect of Microsoft software. Also happens in Visual Basic quite a lot.

XPS in Windows

XPS is the XML Paper Specification. Here is the XPS Team Blog. It's a complementary technology to WPF although it can also be used in unmanaged Win32 applications.

Just Want to Do Simple Diagramming

How to do? Visio is expensive and huge. MS Paint can't draw nice arrows. Will PowerPoint do? Ppoint can make pdfs also and has nice arrows. What about the open-source Dia? This has nice arrows too, if you draw a line it draws an arrow by default, a nice feature. If you right-click the line you get various arrow properties like line-width etc. Cntrl-S will save your file in ".dia" format. If you want to save in bmp for example, you need to hit Alt-FE (File->Export) and select Pixbuf[bmp]. Cntrl-Z is a very useful shortcut for Undo (the usual Windows Alt-Backspace doesn't work) especially if you create a shape that occludes and existing shape. Unlike Paint, you can edit written text using the "Vertical Bar" (Text edit tool).

INDIRECT, ADDRESS, MATCH

What is INDIRECT for?

INDIRECT: This is for creating cell or range references on-the-fly.
Example: If A1 has the value "B2" then INDIRECT(A1) will return the value in B2.

Limitations of Sql Server Express

The key limitation of Sql Server Express is:

1. Maximum 4 GB database size.

Other limitations: uses only one CPU at a time even on a multi-core machine, maximum buffer size 1GB, data/import export feature disabled.

Word is Mad - Why do Page Numbers start at Nought?

Insert->Page Number->Format Page Numbers to correct this madness. Since when did Page 1 become known as Page 0?

What happens when you press return in Excel?

After entering data in a cell, the cursor usually moves vertically down to the next cell. The cursor can also move laterally (left or right) after data entry.

This is configurable from Excel Options->Advanced->Direction, but also from VBA as well.

The setting is controlled by the MoveAfterReturnDirection property on the Application object, which can be set to xlDown, xlToLeft or xlToRight. Doing TypeName on xlDown you will see it is defined as a Long. We can therefore define a function with a static Long that can switch input mode via a custom Keyboard Shortcut.

Sub SwitchCursorMode()
Static status As Integer
Dim msg As String
If status = xlDown Or status = 0 Then
status = xlToRight
msg = "Move Right After Data Entry"
Else
status = xlDown
msg = "Move Down After Data Entry"
End If
Application.MoveAfterReturnDirection = status
End Sub

Shove the above into Excel and then assign a keyboard shortcut. The msg string is not needed, just there for debugging.

Automatic Percent Entry

Excel Options->Advanced->Editing Options->Enable Automatic Percent Entry

Debugging the Error "Reference Source Not Found" in Word

When you re-number Figures or Tables in Word, you may break existing hyperlinks to those sources within the main document text. This leaves a splattering of "Reference Source Not Found" across the document. How do you fix these?

The answer is to create new hyperlinks. You can certainly insert hyperlinks to locations inside a Word doc (e.g. hyperlink to the Biblio section) but what about a specific Figure or Table?

One way could be bookmarks. Bookmark the location you are interested in and hyperlink to it. You can hyperlink to headings or bookmarks within your document. The problem is these bookmarks have certain naming limitations (e.g. no spaces are allowed).

The best way is to do "Insert->Reference" then select "Reference Type=Figure", for example, to refer to a Figure in your doc. This creates linked text; you just need to hit Control key and click to follow the link.

Learn Bibliography Management in Word (APA Style) - Connection with Simple Multicast Networks

Ah. For many projects past I have oft-ignore the bibliography manager, mainly because there was a learning curve, but also because I half-expected most of the exciting "Insert Citation"-type functionality to appear in the "Insert" section of the Ribbon rather than the "References" section of the Ribbon. Discovering the "References" section is to say the least a revelation.

"References" is where you control your bibliography and table of contents. Here is a list of the subsections in the "Refs" tab.

1. Table of contents - self-explanatory
2. Footnotes - ditto
3. Citations and Bibliography
4. Captions - for Figures etc.

Let's talk about Citations and Bibliography. Should these link in direct with footnotes?

First in Biblio sub-tab, select the APA styles, this is a nice and concise biblio-style. APA stands for American Psychological Association and is the common style for citing papers in the social sciences. Chicago style is more long-winded, printing out people's full names - who has time?

To add a source, click on "Manage Sources" and click New (or simple Alt-N). To get into "Manage Sources" you can type Alt-SM ("Sausages and Mash"). Authors can be designated "Corporate Authors" e.g. when a trade association etc. commissions a report. If you are translating footnotes into Sources, then scroll down the page to ensure the footnotes are visible at the page. Then Alt-SM, Alt N (SMN -> Simple Multicast Network). This is because Source Manager takes most of the screen and is fixed size and Always On Top.

Source Manager can distinguish between a number of types of sources, for example: in order of publishing reputability - Journal Article, Article in a Periodical, Report (e.g. draft articles, not submitted to a journal).

There is also built-in logic for handling web sources. From market data websites you can cite the source as "Corporate Author" and type in the Company Name, which will be used to "index" the reference.

Page Count in Word 2007

It's not as easy as just Insert->Field. There are a few more pointless steps to do first.

1. Insert->Quickparts->Field
2. Document Information->NumPages|NumWords
3. Select a numeric format and hit ok

Note: these fields don't update by themselves. You must right-click and hit "Update Field".

Oversized Word-Generated PDFs

Usually this is due to specific graphics. Copy into Paint, Recopy and Regenerate.

Create a TOC in Word and Keep it up to Date

Insert->Cover page with TOC should do the trick. But what happens when TOC needs updating? Right click - then update.

Why Word Formatting and Content Control Sucks

Sometimes Word prevents you deleting whitespace. To resolve this, jump into Outline mode and delete the space manually.

The Hidden World of Excel Charts

How on earth do you add a title to an Excel chart, which is by default created sans-title? Nothing to be seen in the context menus...mais non, a simple click in the "Plot Area" and a "Layout" option appears in the top menu..from which you can create a new title. But to add problems to more problems, it offers you the option to make the title "Centred Overlay Chart" - how stupid and useless! The option you want is "Above Chart". Madness in Design. The Mad Hatter is at it again.

Q: What is WMI in C#? A: System.Management

WMI is Windows Management Instrumentation which manifests in dot net as System.Management and System.Management.Instrumentation (a la JMX).There are some specific limitations to using WMI in a dot net framework. The main cool feature of WMI is the ability to monitor events on a network, for example when disk usage gets to 80%.

DDI Hook Punch

Kaplan introduces some interesting techniques like DDI Hooks on internals.com. He also has a patent application in the field of API hooks. In his article he refers to DDK (Driver Development Kit). This toolchain has now come to be known as WDK (Windows Driver Kit).

Madagascan Lemur Regression In New Excel and Adjusted R-squared

Accessing Excel's Awesome "Regression Engine"

Alt-AY2 (AyeAye - a form of Madagascan Lemur)

This brings up the "Data Analysis" listbox with a range of statistical features inside including Random Number Generation and Analysis of Variance.

Type R three times (one to skip Random Number Generation, the second to skip Rank and Percentile and the third to access Regression).

Interpreting Results

The output of Regression is a summary table of "Regression Statistics". Here is a summary of what the statistics mean:

* Multiple R: This is the Coefficient of Simple Correlation. It is the positive square root of the R-squared
* R-squared: Coefficient of Simple Determination. According to wikipedia, it is "the proportion of variability that is accounted for by the statistical model" or "percentage variation in y that is explained by percentage variation in x"
* Adjusted R-squared: R-squared adjusted by the number of predictors (R-squared tends to overestimate the strength of the association, especially when there are >1 independent variables)

What is the relation between coefficient of correlation and the coefficient of determination? One is the square root of the other. So if R-squared is 1%, then the "Multiple R" or "coefficient of correlation" will be 10%.

Standard Error

Standard error of an estimate tells you the accuracy from your prediction.

Troubleshooting

Regression - LINEST() function returns error - usually because ranges are different sizes.

New England Excel

New Keyboard shortcuts in Excel 2007+ are as follows. The can be remembered by the following acronyms for New Delhi, Newcastle University, New Mexico. For details on how we originated these acronyms please see acronymfinder.

1) Traffic Chaos. New Delhi Scatter Plot: Draw Scatter Plot: Alt-N D.
2) Lion and the Cross. Newcastle University Symbols: e.g. letter-e-with-accute-accent: Alt-N U (Newcastle University).
3) Water flows in New Mexico. New Mexico SmartArt: Alt-N-M allows you to insert some very clever flowcharts (cycle or process diagrams)
4) Ancientness of Maths. New England, oldest clearly-defined region of the United States. Alt-N-E, Insert Equation.

Now for the Viewing Modes.

1) Water Level View: The Normal View. Alt-W-L
2) Washington Post View. Page Layout View. Alt-W-P

Now for Macros.

1) Land Rover Macro. Alt-L R to record a new macro. Give the macro a suitable name like "ReformatChart" rather than the default "Macro1".

Starting a Second Message Loop on a Single Thread

System.InvalidOperationException: Starting a second message loop on a single thread is not a valid operation. Use Form.ShowDialog instead.

This happens when you have more than one class trying to build a form and draw it.

Spreadsheet Analysis Techniques

Put formula into notepad and make font big.

Excel Column Grouping Action

Data menu -> Group (after having selected the desired columns).

Yucky Bug in Visual Studio Intellisense

By default, autocompletes with a semicolon. Yuck! Deactivate this ASAP.. Options menu->Text Editor->C#->Intellisense and delete the semicolon from the completion list.

You Really Need NUnit

Unit test you DLLs! Try Nunit.

Building Blocks of Data Binding

Stop fumbling around trying to recall the key building blocks of ADO.NET and Dot Net's data binding architecture. Here they are (fresh from System.Data.dll):

1. DataTable
2. DataColumnCollection: defines schema of DataTable
3. DataColumn: building block of schema of DataTable

Adapters create DataTables which are attached to BindingSources that get strapped on to Controls.

Key properties on DataColumn include:

1. ColumnName
2. DataType: e.g. Decimal, Double, Byte, Char, DateTime (or System.Type.GetType ("System.DateTime"))

What is Ngen?

Ngen.exe is the Native Image Generator in .NET. Compiled native code gets put into a native image cache. It can be used to reduce startup time for an application.

Random Numbers

Random r = new Random();
r.nextDouble(); // number between 0.0 and 1.0

The "algo" is based on Knuth subtractive random number generator. Random number generator is seeded based on system time. Random number generators generated in close succession will generate the same numbers.

To avoid this problem, use a single (potentially static) instance of Random.

Why is a Dictionary better than Two Arrays of keys and values?

Dynamically sized.

Lowdown on C# Dictionaries: KeyCollection Is the Actual Keys

C# has no hashtables worth speaking about - it has dictionary objects. These live in the System.Collections.Generic namespace. (The C# Hashtable class does exist but it's non-generic which means objects are stored as type Object with associated cost of boxing and unboxing).

Retrieving the keys from a Dictionary yields a KeyCollection, a non-inheritable (or "sealed" class in the C# jargon), which is not a static copy of the keys but pointers to the actual keys.

In concurrency terms, KeyCollection supports multiple concurrent readers as long as the Collection is not modified. Enumerating through this collection is not inherently thread-safe unless the collection is locked.

A key question is how do you cast the KeyCollections and ValueCollections within the Dictionary object. Both are implementing the ICollection interface, which has two methods: CopyTo (copies the elements to an Array, that also implements ICollection) and GetEnumerator.

StreamReader vs. Stream

The System.IO namespace is one of the heavily utilised namespaces in .Net programming.

StreamReader is for character input, Stream is for byte input.

StreamReader is the standard tool for reading text files.

The common pattern is to create the StreamReader inside a using clause (e.g. using (StreamReader sr = new StreamReader("file.txt")) which takes care of closing the reader automatically as well. Lines are read by calling sr.ReadLine() in the context of a while loop (the string that represents the line must be declared outside the loop) - otherwise you get an Invalid Expression error. An "SR" can be init'ed from a Stream or from a string.

A StreamReader is NOT thread-safe by default.

TextReader.Synchronized is a thread-safe wrapper.

TextReader is the abstract class implemented by StreamReader. TextReader and Stream are really right at the top of their respective inheritance hierarchies; they both extend MarshalByRefObject (MBRO) and IDisposable (interface pattern for releasing unmanaged resources). MBRO is used for communication across application domain boundaries.

Reading Ebooks using Adobe Digital Editions

Adobe, the people that brought you PDF files, now has a product called Digital Editions which allows you to read e-books stored in .acsm format. It also supports PDF/A and EPUB formats (EPUB is developed by IDPF).

Configuring for Productivity: C# Formatting Style for .NET 4

In Tools->Options->C#->Formatting:

1. New lines for option braces - all unchecked
2. New lines for option keywords - all checked
3. New lines for expressions - only thing checked is:
"place query expression clauses on new line" (SQL)

Summing Absolute Values in a Range in Excel

This is SURPRISINGLY not so straightforward. You can't just do:

SUM(ABS(A1:A1000))

(you will get hash-VALUE in the cell) unless you do Cntrl-Shift-Enter to convert the expression into an array formula (as opposed to a scalar formula).

This trick doesn't work though if you're using SUMIF e.g. SUMIF( rangeA, "=gazillion", abs(rangeToSum)). That won't work. You'll get an unhelpful message saying the formula you typed contains an error.

Numerical Spreadsheets: Respect for Maths

Many punks build numerical spreadsheets quickly but don't think about the quality of the numbers. Building a wrong spreadsheet fast is no achievement. Sometimes it can be done with general purpose software just to get a prototype working. However, when dealing with systems that generate numbers, respect must be given in the form of time taken to generate accurate numbers.

The Art of Focus and Fighting the Timeline War

Sometimes, especially if implementing mathematics in Excel, such as some statistical modelling spreadsheet, it is natural to have wished to have studied the underlying mathematics in a more systematic fashion. Do not be disheartened, progress regardless and review the work once complete. Progress should be the first priority. "Tag" task like doing things systematically for later.

Eliminating the Office 2007 Configuration Wizard

Details here. Involves messing with Event Viewer.

Hardcore Office: PIP files

What is the PIP file?

C:\Program Files\Microsoft Office\Office12>dir *.pip

shows up ACCESS.PIP, EXCEL.PIP, INFOPATH.PIP, WORD.PIP, OIS.PIP etc. PIPs are Office Personalised Settings files.

Normal.Dot Demystified

Normal.dot is Word's master template.
There are two basic files in Word. One is a .doc the other is a .dot.

HIDDEN FEATURE: Creating Icons using Paint

MS Paint -> Copy to -> Select File Type BMP -> file name .ICO. This will convert any graphic in Paint into an icon. This was a great revelation. With it one can grab company logos and convert them into icons.

Childhood Games that are Super-Useful for Computer Programmers

Spot-the-difference - useful training for detecting syntax errors and problems with cut-and-pasted code

Project Scheduling in Windows

For you project scheduling needs, consider MS Project. They have come out with an awesome new 2010 version. The buzzword is "Prioritize, Align and Execute Effectively".

For those on a lower budget, perhaps a low budget Gannt chart maker could do nicely.

Mind the Brackets: Coding Formulas in Excel

When coding formulas in Excel (using VBA or an Excel worksheet function) make really really sure that brackets are in the right place. Wrongly grouping expressions leads to erroneous results!!! Know your formulas inside out. Practise the calculations thoroughly with a calculator first if possible. Cultivate the ability to spot bugs in formulas quickly!!!

Use of TABLE worksheet function to do What-if Analysis

Microsoft Excel has some tools for performing what-if analysis, most useful in financial planning. (Of course, since TABLE is a worksheet function, it can't be used as a UDF name).

Excel's "Advanced" Display Options (Worksheet Focus)

Perusing Excel's "Advanced" Display Options via the Microsoft Office button demonstrates three different categories of Display Options ("Display", "Display options for this work&book", and "Display options for this work&sheet"). Keep hitting "Alt-S" until you get to the worksheet display options. Here you can make page breaks disappear and gridlines disappear also. There is also an option to show zero in cells that have a zero value. There is also an explicit page break view which you can access from the (now-familiar) view portion of the chaotic Ribbon.

Statistics Action in Excel

Excel worksheet functions for the Normal Distribution

NORMDIST - return the CUMULATIVE normal distribution
NORMSDIST - return the STANDARD normal cumulative distribution (Z~N(0,1))

The Mysterious Type Declaration Characters of VBA

Suppose you are writing a calculation in VBA. You may notice a mysterious "type declaration character" appearing after the number you have typed. This could be one of several possibilities.

# Double
$ String
@ Currency

Debug.Print Demystified

You can insert Debug.Print statements anywhere in your VBA code. These will appear in the Immediate Window in the VBE (just strike Cntrl-G to display the results of these statements). From Excel 2000, you can also do Debug.Asset (condition) which will break the code if the assertion is violated.

Debugging unfamiliar VBA can be made easier by the use of Debug.Print.

For example, macros may hang and go into infinite loops. This can be due to lack of "Option Explicit". For example, suppose you loop through the numbers I from 1 to 30. You call a function that also uses the loop counter I, which causes I to get stuck at 15 in your main loop - infinite loop! Debug.Print can help you find these problems in loop counters quite easily.

Cntrl-G is your friend!

If you want to take VBA debugging to the next level, you can also use conditional compilation using the #const directive, together with #if and #end if macros. for example:

#CONST DEBUG_ = True
#If DEBUG_ Then
    Debug.Assert (X<109)
#End If

Excel 2007 Keyboard Shortcuts

Inserting a new row in two steps

Shift + Spacebar: Select the current row
Cntrl + Shift + Plus Sign:

Excel VBA Run-time Errors and Run-time Error Handlers

We've seen compile errors in Excel VBA. How about some run-time errors?

Run-time error '1004'
AutoFit method of Range class failed

If you do Selection.AutoFit, instead of Selection.Columns.AutoFit. In Dot Net, this kind of cr- gets caught at compile-time. As an interesting side, you can explicitly set the column width in terms of number of characters.

You can develop your own run-time error handler and reference it using "On Error Goto (Insert Name of RTEH here)" but please note it will not be re-entrant.

Engineers versus MBAs

A post.

Excel Macro-Enabled Workbooks in 2007, Worksheet Functions and Error 51

What's the game with xlms files (Excel macro-enabled workbooks)? Why do I need to save to a "macro-enabled" file to use macros?

The purported justification is added security.

Microsoft are willing to complicate programmability (the key strength of the OLE architecture) under the pretext of security.

A short-term solution is to save as Excel 1997-2003 workbook.

Ok, so why are we so bothered about this.

Let's say we want to create a new Excel worksheet function. We can create this in a module. Suppose we make a function that computes sales commission which returns 5% of whatever number the user puts in. Easy enough. If you didn't define a variable, you'll get an error "Variable not defined" (assuming Option Explicit is set, almost always a good idea).

Suppose you write a worksheet function and a message box appears: "That function is not defined". What does it mean? Usually, it means you have created a function using a reserved keyword in VBA. For example, you can't define a worksheet function called step, because step is a VBA reserved keyword e.g. For I = 1 To 10 Step 2.

Taking Square Roots

To compute a square root in VBA use the SQR function. This is the same as SQRT in an Excel worksheet. Oftentimes, you'll find the raw VBA method is somewhat different to the worksheet function.

Taking Logarithms

In VBA, log means natural logarithm. In a worksheet, log means log to base 10. Alternatively you can use log(number, base).

Other things "not defined"

We've seen the error "Function not defined" now suppose you open your workbook one day and it says "User-defined type not defined". Problem is - you can't find the location where the code is breaking. Even shoving a breakpoint in where you think the problem is doesn't help.
Why? Because it's a "Compile Error" not a run-time error! First step, identify all type definitions in your VBA modules (they start with the prelude "Public Type" or "Private Type"). Then see all usages. Next check WHERE the Type is defined. Are there functions defined before it? Try commenting out those functions.

You do the steps above and try to rerun your program (I avoid the dimunitive term macro). Now, the Type error vanishes, but the next time you use the type you see the message "Compile Error: Internal Error". Out of curiosity, click Help and you will see a description of the error: you will see it is "Internal Error (Error 51)". Sheepishly, the docs ask the developer to "make sure (the) error wasn't generated by the Error statement or Raise method". The explanation and solution is given as: "An internal malfunction has occurred in Visual Basic .. contact Microsoft Product Support Services to report the conditions under which this error appeared". This is an error in the Visual Basic symbol table caused by the order in which you define things. Visual Basic requires you to define objects in the order: Types, Functions and Functions of Functions. Messing up the order (e.g. by so-called "refactoring" - one of the most stupid terms in computing) or missing a definition that the compiler doesn't catch can result in Error 51.

From Application Software to Operating Systems: The Awesome Story of David Cutler

Mark Russinovich is the kingpin of Windows troubleshooting. He sold a company called SysInternals to Microsoft in 2006. In a 1998 article in Windows Magazine, Mark talked about the history of NT and its intellectual origins in the progamming backwaters of DEC. In the article he describes the background of David Cutler, Chief Architect of NT, and part of the original VMS development team. He made a transition from writing application software at DuPont to writing operating systems at DEC. Digital announced VMS in 1977. Cutler was restless by 1981 and threatened to leave Digital. In response Digital gave Cutler 200 hardware and software engineers - Cutler moved to Seattle (Home of Starbucks) and started a dev-center out there in Washington State. Their mandate was to develop a new CPU architecture and OS for the 1990s. In 1988, Cutler's project was cancelled and MS execs leaped in to grab Cutler, along with 20 engineers from DEC. The result was Windows NT. It took over 200 developers and testers to bring it into fruition. With NT came the awesome new Win32 API, the 32-bit evolution of the 16-bit API of Windows 3.0.

Usenet joke: If you increment each letter in VMS you get WNT.

Subverting the Office 2007 Keyboard Shortcut Time Lag

To access MS Office Button (the moral equivalent of the File Menu), the keyboard shortcut Alt-F takes you there. This (quite often) displays a time lag with respect to the previous versions of Excel. Part of the reason can be attribute to the more intensive graphics (for example when you hit Alt, a bunch of labels get illuminated in the Ribbon). You also need to be au fait with the Escape key to disable any menu selection (for example if you have hit a bad shortcut and want to reset your settings so you can attempt a new k/b shortcut).

One way of dealing with the time lag is to allow for it when you use keyboard shortcuts. For example you can do the following keystrokes: 1) escape, to disable any previous menu selections, 2) hit alt -> pause for 2s, 3) hit F. This is ridiculous but is one way to guarantee successful execution of a k/b shortcut.

The most useful interactions with the Button is access to the "Excel Options" page. To access type: Alt-F-I (Alt-Fixed-Income (Options)). Quick guide to the sections:

1. Formulas - allows you to set automatic calculation on or off.
2. Proofing - enables you to disable autocorrect.
3. Advanced - allows you to configure the supposed multithreaded calculation.
4. Trust Center - followed by Alt-T (Trust Center Settings) allows you to set Privacy Options on your workbook.

Cntrl-F1 allows you to minimise or restore the Ribbon (like an HUD).

Volatile Functions in Excel

Example: RAND worksheet function

Requires creation of an automation add-in.

VBA Annoyances

CONDITIONAL BREAKPOINTS

You CAN'T SET conditional breakpoints in Excel VBE...but you can SIMULATE one with the if statement.

PRIVACY WARNING WHEN SAVING WORKBOOKS

Privacy warning: This document contains macros, ActiveX controls, XML expansion pack information, or Web components. These may include personal information that cannot be removed by the Document Inspector.

How to I get rid of this tripe?

Excel Option > Trust Center > Trust Center Setting > Macro Settings > click Enable All Macros ....

Excel Option > Trust Center > Trust Center Setting > Active X Settings > click Enable All controls without restriction ....

EXCEL 2007 CLAIMS TO DO MULTI-CORE CALCULATION

Where is this functionality at the VBA level.

NO VOLATILE KEYWORD IN EXCEL VBA

Boo!

NO NOTION OF CLOSURES IN BUILDING WORKSHEET FUNCTIONS

Closures would be real useful when implementing mathematical models in Excel.

Microsoft Office Documents Storing Personal Data

Office docs may contain some PII (personally identifiable information). If so, you may get errors when trying to save documents (Document Inspector will complain!)

Mastering MS Word 2007: Footnotes versus Endnotes

References->Insert Footnote. (It's the big button, to the left of Insert Endnote - yes, I know it's confusing). I prefer Footnotes to Endnotes any day.

Excel VBA Tips and Tricks

Some random coding tricks of Excel VBA.

Internal Speech Marks

To include speech marks in a string, just precede the "internal" speech mark with another speech mark. Example: s= "Head of the ""Global"" Team".

Adding ActiveX Controls to Worksheets in Excel 2007

This is easy. You just need to know where to find the Forms menu. Click on Developer tab, and Insert.

Mark of Experience (Code == Code)

Reading code and not caring about coding style. Used to so many different styles of coding.

Excel Ranges

What is the difference between Range.Value and Range.Value2? They are basically identical except that Value2 doesn't recognise currency or date objects. So dates would be translated into integer values..

The Unofficial Rules of Software Development (Coding)

Technical and Mechanical Rules

So, you're suffering from programmer's block? No worries let the following precepts help you.

1. If you write the interface the implementation writes itself.
2. If you write main() the program follows.

Here are some more maxims to guide and inspire professional software development:

1. Do not be deterred when people desire to see code rather than understanding. Focus on understanding and the code will come.

2. At the same time, do not ignore implementation.

3. Once implemented, reflect. Could I have done things better. What was good about my design, what could be improved. Could I have done the same job FASTER, MORE EFFICIENTLY.

4. Writing properly structured and well-documented code is a great skill.

5. The most satisifying coding sessions are those that start when you are hazy and fuzzy about a subject and progress to a stage of clarity, at least clarity about what you were confused about to start with.

6. Coding sessions through the night is like going through a long, dark tunnel. You need faith and hope to keep you going.

7. Program night or day, summer or winter.

8. Michael Dell: "Celebrate for a nanosecond then move on". There is always an interesting problem waiting to be solved.

9. Creativity, the desire to solve unsolved problems, or to solve problems more efficiently than people of the past, that is what drives programmers.

Freewheeling

How to become freewheeling in programming? Programming should be as easy and straighforward as playing in sand. But to achieve this requires long hours of practice and analysis. Sometimes you get programmers block and nothing seems to work. Consider taking a break, watching television, a mystery drama or some such. Amazingly, after a period of mental relaxation (not sleep but relaxation) the mental block may be removed.

User "XYZ" is already using Sheet.xls - Open as Read Only?

Sometimes Excel crashes (2007 particularly during OLE operations). Open task manager and kill off all Excel processes then re-open the spreadsheet.

OLE is really broken in Office 2007.

When do you work best as a programmer?

Some people have times of day when they are most productive e.g. late at night, or early in the morning, when they are more free from distractions.

Debugging Excel VBA (F8 is your Friend)

F8 Step Into
Shift-F8 Step Over

The VB interpreter has three states: "NOT RUNNING", "RUNNING", "BREAK". If Excel appears to have frozen, check that the VB interpreter is not hanging in the break state.

Excel VBA Runtime Error Handling

Consider the following statement in Excel VBA:

I = 3/2

the answer is either 1 or 1.5, depending on the data type of I (i.e. Integer or Double). If I don't declare I, it will default to Double.

However, if I do

I = 3/0

I will get a message "Runtime Error 11: Division by Zero". How should errors like these be handled? Excel VBA does not have a try-catch-finally yet, so we need to use the built-in error handling mechanism.

VLOOKUP, HLOOKUP and other Friendly Excel Functions

Lookup formulas look up values in tables. Think of it as searching for a name in the telephone directory.

Example:

VLOOKUP( B2, TelData, 2, FALSE )

means lookup the value B2 in TelData, and return the 2nd column of TelData, ASSUMING a row has been matched. Setting the fourth argument to FALSE, means find an EXACT MATCH.

Worksheet Headers and Footers in Excel 2007

Click on the Insert menu. Then click "Header and Footer" (button should be next to "Text box"). To make headers/footers visible and invisible go to View menu and toggle between "Normal" and "Page Layout".

Office VBA

Office VBA
Go into VB Editor and hit F2. This gives you the "contents" of each module.

Excel Developer Tab (Design Mode)
What if you are hacking an Excel spreadsheet and want to see the code behind a button? For some reason, the "Design Mode" button is inactive!

Look below the "Ribbon" to see if there is a "Security Warning" saying "active content has been disabled". Click on Options, and 1) Enable Macros and ActiveX, 2) Enable Links.

The Pain of Creativity Versus the Pain of Implementation

Why can implementing something so simple be so painful? Building simple programs to help you reason about simple concepts - that should not be painful should it? The need to dutifully pursue a single line of investigation is painful only insofar as it prevents one from pursuing alternative lines of investigation that seem more attractive to the mind. What can offset this impediment to motivation? The prospect of understanding simple and perceptually mundane things with increased clarity should be sufficient motivation to pursue the simple but seemingly dry undertaking - building simple programs to reason about simple concepts.

A lot of seemingly boring things are interesting when you go deep into it. One useful rule is if you are stuck for motivation, take the topic and think - not why is it boring, but why is it interesting? Why do I want to do this task? What will it give me? Identify three things to motivate you and carry on.

How Do you Record Macros in Microsoft Word 2007? (Developer Tab)

Instead of Alt-TM

To record a macro, you first need to "Enable the Developer Tab".

To do this:

1. Click File
2. Click Options
3. Customize Ribbon
4. Check the Developer tab under Customize the Ribbon

Sorted.

To Automate or Not to Automate that is the Question

The problem with writing about technology is that anything you write becomes rapidly outdated. Not so the dilemma of automation. This dilemma is ever-present. It boils down to the following question - is it worth automating every small task? Small fiddly tasks tend to recur quite frequently e.g. a table formatting algorithm for moving data from Excel to Word. Is it worth automating? It depends on your time constraints. You are sacrificing short-term for long-term benefit. Automation requires intellectual effort. Intellectual effort that could arguably be used more productively understanding and executing the task at hand rather than automating it.

Word 2007 Reviewing

How do you get out of document reviewing mode in Word?

Mastering Microsoft Equation Editor 3.0

It Starts with "Insert Object"

In Word 2003 (and also in Word 2010) Alt-I-O will insert an "Object".

Click this and you get a List Box from which you should select Microsoft Equation 3.0 (hit M a few times and you'll get there).

The first tricky thing you need to get used to is underbar/overbar distinction versus fraction/radical template. If you want to write a partial derivative you need to use the fraction/radical template (LHS), underbar/overbar is RHS.

Greek characters and Miscellaneous Symbols

The symbols available from top row (RHS) give you Greek characters (UPPERCASE), Greek characters (LOWERCASE) and Miscellaneous symbols.

The "Modified Delta" Symbol

The "modified delta" symbol for partial derivatives is not a Greek symbol per se but is derived from the Greek lowercase delta with the curly head, and was introduced by Adrien-Marie Legendre (a top flight Parisian mathematician) and popularised by Prussian mathematician Jacobi (famous for elliptic functions, functions on the complex plane, discovered as the inverse of elliptic integrals).

So MS Eqtn Editor is quite correct in classifying this symbol under "Miscellaneous".

Windows in the Grand Duchy

Does Bill Gates ever take a moment to meditation about his customers in the Grand Duchy of Luxembourg? The North is wooded country of great scenic beauty. The center and south is farmland and woods. The historic capital is Luxembourg city. Nazi Germany used Luxembourg as a transit territory to attack France (outflanking the Maginot line).

Organize Your Web Life Korean Web 2.0 Style

TEDxSeoul is an independently organized TED event in Korea, publicised on Korean Web 2.0 Blog web20asia.com. Among the fun things they have is lemonpen which is basically MS Word sticky notes for the web (report here). Another web notepad is OpenMaru's SpringNote. Other companies that go beyond mere social bookmarking or notepad-style clones are Diigo, Fleck, Stickis and Blue Organizer. Diigo has the benefit of being featured in cnet's GetSmart column. Another feather in the cap for the hacker "on-the-road". More organizing tool reviews can be found on readwriteweb.com's web page.

Dot Net welcomes Dynamic Languages

Read more on Jimmy's blog. Also, check out IronPython's Dec09 release on codeplex.

Product Key Card Pricing and Office 2010

Customers will pay up to 30% less for the non-boxed version of MS Office - Computerworld magazine has run an article on how this pricing scheme will help to turn boxed software into an endangered species. This scheme is called Product Key Card Pricing and will involve you buying a plastic card with a code on it, which you use to "unlock" the software.