If you’ve come across this already then this might be obvious, but in order to mount a USB external drive on an Ubuntu VirtualBox guest running on a Mac OS X host, you need to unmount the drive in Mac’s Finder first. Then using either click the USB icon in the status bar in your Ubuntu guest and you’ll see the drive un-greyed out (when it’s mounted on the Mac it appears greyed out and you can’t select it) – click it and it will mount automatically. Or you can do the same thing from the VirtualBox menu, Devices/USB.
Eclipse error: Access restriction: The type ‘xyz’ is not API
Eclipse has some pretty bizarre error messages that really don’t tell you exactly what the error is or how to fix it. This weekend I saw this one for example:
Access restriction: The type 'xyz' is not API (restriction on required library ...)
A quick Google told me what this actually means is that I have a line of code using a JDK API that is not in the currently selected runtime for the current project, but does exist in other available runtimes.
For example, when setting up a project with a Maven pom.xml, if you don’t explicitly specify what JVM version you want for the project, you get Java 5 by default.
There’s a couple of different ways to change the JVM version using Maven, but the approach I prefer is by adding properties (because it’s more concise than configuring the Maven compiler plugin):
<properties> <maven.compiler.target>1.8</maven.compiler.target> <maven.compiler.source>1.8</maven.compiler.source> </properties>
Alternatively if you’re not using Maven, just change the JRE System Library in the project settings on the Java Build Path/Libraries tab (remove the one that’s currently there and add the version that does have the APIs that you’re using, most likely a later version).
Adding the Java SE 8 patch for Eclipse 4.3
Eclipse 4.4 Luna versions already have SE 8 support included, but for Eclipse 4.3 you need to add a patch to add support, which is described here.
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