Skip to content
Home ยป excel

excel

VBA: fix “compile error: expected variable or procedure, not module”

I recently had to edit somebody’s VBA macro in Excel for a recurrent data cleaning task. The code was a mess because large chunks of it were created using Excel’s macro recorded. While structuring the code in subroutines and modules, I ran into an error. In this blog post I… 

Error in .jcall(“RJavaTools”…) when importing large xlsx files in R

Recently I had to read in a folder full of large Excel (.xlsx) files. I did as I usually did and use the xlsx library. However, reading in the largest files produced an error: “Error in .jcall(“RJavaTools”, “Ljava/lang/Object;”, “invokeMethod”, cl, : java.lang.OutOfMemoryError: Java heap space” I don’t know what cause… 

Date conversion from Excel to R

  • by
  • 1 min read

It happened too many times before I write this blog post. Oftentimes, when I read Excel (xls or xlsx) files into R, I encounter this strange phenomenon where dates are converted to a 5 number integer. Here is how to fix it. For example, 02/01/2017 (dd/MM/yyyy) would be converted to…