In a move surely aiming to ensure no-one has a reason to cling on to their ‘old’ version of Windows (XP users, thinking of you, but unfortunately you’re out of luck for this upgrade), Microsoft will give free upgrades to Windows 10 this summer, for all 7 and 8.x users.
Microsoft .NET now Open Source?
This is a turn up for the books. Who would have thought this would even have been a option a few years back. Microsoft is open sourcing .NET, making the source available on GitHub, and even plans to make the framework cross platform to run on Linux and Mac – more here.
One can only speculate that they feel pressured to do this for .NET to stay relevant, and/or to expand it’s usage to other platforms outside of Windows? Given the accelerating trend to deploy to the cloud on virtualized platforms (in most cases on OSes almost certainly not to be Microsoft Windows), maybe Microsoft sees this as a strategic move for them to keep their development framework an option in this changing landscape?
Excel: formulae and VBA tips
By no means an expert, but here’s some Excel tips, useful to remember for future usage:
- Jump to row: Ctrl G, then enter a cell reference, like C10000 – useful for jumping around large sheets
- Jump to end: Ctrl End – jump to the last row with values (doesn’t work if you’ve pasted a repeating formula into a whole column though)
- Sorting a column – if you have a header row, make sure every column has a value in the header row, otherwise the header row will get sorted too
VBA Useful Tips
Basic language constructs
Declaring variables
Dim variable name
Can declare vars as a type with As typename
Conditionals:
If condition Then 'do stuff End If
If condition Then 'do stuff Else 'do stuff End If
Iteration:
For value To upperValue ' Do stuff Next
While condition 'do stuff Wend
Logic Operators
= equal
<> not equal
And
Or
Not
Example:
If somevalue = othervalue Then 'do stuff End If
Line continuation use _ at end of line
String Concatenation: string1 & string2
Subroutines:
Sub name() 'do stuff End Sub
Functions
Function name As returntype 'do stuff name = return value ' assigning the return value to the function name returns the value End Function
Other Useful Stuff
Get number of populated rows in a sheet
Sheets("SheetName").UsedRange.Rows.Count
If you have values inserted by a formula that are displayed as #N/A, you can test for this with:
isError(cellreference)
Clearing sheet ranges
Sheets("sheetname").Range("A1:B1000").ClearContents
Building a Range with concatenated values from Strings
Range("A1:" & "B" & CStr(someStringValue)).Select
Copying a range from one sheet to another
Sheets("sheet1").Select Range("A2:B100").Select Selection.Copy Sheets("sheet2").Select Range("A2").Select ActiveSheet.Paste
Remove duplicate rows in a range
ActiveSheet.Range(Range("A2"), Range("C100")).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
Copying a cell value to a variable
Dim somevalue someValue= Sheets("sheet1").Cells(rownumber, "F").Value
Trimming spaces
Trim(somevalue)
VBA VLookup – using a declared range name on an existing sheet
lookupvalue= Application.VLookup(value_to_lookup, Range("a range name"), column_number_for_decode_value, False)
Function to lookup row contain last populated value
Function getLastPopulatedRow() As Long Dim lastCellInColumn As Range Set lastCellInColumn = Sheets("sheetname").Cells(Sheets("sheetname").Rows.Count, "A").End(xlUp) getLastPopulatedRow= Application.WorksheetFunction.Max(lastCellInColumn.Row) End Function
Inserting a formula into a cell and then copying it down a column
Sheets("sheetname").Cells(2, "D").Formula = "=SUM(O2:U2)" Sheets("sheetname").Range("D2").Resize(lastRow - 1, 1).FillDown
Using the value of one cell to lookup a value in another
I used this approach to find the highest value in a range of cells, and use that to return the heading label for that column:
=INDEX(G$1:M$1,MATCH(LARGE(G2:M2,1),G2:M2,FALSE))
In words, starting in the middle, this is finding the largest value (1, change this param for the nth largest) in range G2:M2, finding the relative position of the value in the range G2:M2, and then using that as an index to the range G1:M1 which contains the column headings – the value returned is the column heading that contains the largest value in the range G2:M2.
Disabling Excel Features During Long Running Macros and Formulae
If you’re dynamically writing content or updating content covering thousands of rows, sometimes Excel can slow down as it formulae are re-evaluating based on the dynamic updates. To work around this, there are features to can turn off:
Sub disableStuffForPerformance() Sheets("sheetname").EnableCalculation = False Application.ScreenUpdating = False 'this setting makes the most difference increasing processing speed Application.Calculation = xlCalculationManual Application.EnableEvents = False End Sub Sub turnDisabledStuffBackOn() Sheets("sheetname").EnableCalculation = True Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub
April 8th: End of Windows XP Support and Windows Updates
It’s finally here… April 8th, the end of Windows Updates for Windows XP. Has it really lasted this long? Incredible really.