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)