5 (or more) indispensable online coding tools

The last decade has seen an explosion of online tools and services. Especially in the coding world, a lot has happened. If you’ve been stuck in a cave for all this time, or have been coding in your man cave without ever reading developer news, you may have missed out on some real gems!

Here’s a compilation of my personal favorites:

1. Repl.it

This is the Mother Of All Coding Tools. A REPL is a Read Eval Print Loop. In other words, the computer reads what you have created, executes the code, prints the output, and then gives you a chance to go change your code, after which the loop starts again. You could also call this ‘an interactive shell’, but in contrast to normal operating system shells, like the command window in Windows, repl.it offers shells for all kinds of programming languages.

Using repl.it you can code in Python, C, C#, Java, JavaScript, PHP, and also the newest coolest functional programming languages. Of course it also supports HTML/CSS/JavaScript. You can even try the classic BASIC.

On top of this interactive shell repl.it has created a community for teaching, complete with courses, subscribing students and tracking.

http://repl.it

2. JSFiddle and Code.pen

Let me compact this article by naming these two twins in the same breath. They are very alike, and give you the ability to use HTML/CSS/JavaScript as an interactive shell. They do offer more features then repl, but it’s really only for web development.

JSFiddle.netcodepen.io

3. Codetracer and stackoverflow

Again I am placing two tools together, but in this case they have the same purpose yet look totally different. It’s like comparing a katana to a swiss army knife. Codetracer is the katana. It’s a place where you can post a piece of code, and instantly get feedback from others, line by line. Stackoverflow is a giant, and less a tool and more a community. It’s the Google for Developers: every coding problem known to man has been solved before on stackoverflow. But, getting answers on a specific question you should post is horrendous: often questions are downvoted, censored, or outright deleted. On top of that some stackoverflow members like to mock or insult people for not following guidelines or not investigating their problem enough. Simple questions just do not belong on stackoverflow (like: how do I sort this array?).

codetracer.co – stackoverflow.com

4. Debugle and Toggl

Debugle is the most slick and simple interface for listing and tracking your bugs while coding. It’s catered especially for single developers or very small teams. Toggl is the most slick and simple interface for time tracking (hey I get a deja vu here!). Together with Debugle you have an independent developers coding workspace setup in a jiffy.

debugle.comtoggl.com

5. Workflowy

Workflowy is one of those ideas that wakes you up at 3AM but which you have forgotten in the morning: “what if you had a todo list, that could indent, just like code?”

By indenting tasks you can separate tasks from categories, and using workflowy you can really go wild with your creative task lists. Filter by date, by description, by status, you name it. Also entering tasks is so simple it’s a breath of fresh air compared to Outlook or other monsters that make you go through hoops just to write ‘try out several button styles’.

workflowy.com

Using named ranges and worksheet functions in Excel VBA

Bold Brackets

A couple of days ago I saw something in an article on StackOverflow, that blew my mind. I can’t find the article anymore, but I do remember what this one neat trick was, that will for ever change your VBA. It’s called a named range, and I found out I had always been doing it wrong. So have you, most likely.

Have you ever written something like this?


s = Application.WorksheetFunction.Sum(Range("A1:A10"))

You thought you were quite smart, using SUM to add some values together that would have taken a loop in VBA. You petted yourself on the back, took a beer, and applauded yourself for you being awesome. Well, you’re not awesome. This is lame. You suck. Ok, maybe not, but watch this:


s = [Sum(A1:A10)]

It’s incredible! This gives the exact same result.  You may wish to prepend with a sheet name, so it’s an exact reference. You can use any kind of name inside the square brackets. So, if A1:A10 is named ‘records’ in Excel you could rewrite this code to


s = [Sum(records)]

Note that with this notation we don’t use double quotes around the name of the range.

Vanishing Variables

CaptureA quite mighty use for this, is that we can now write code with a lot less variables, if we let Excel do the work for us.

  1. make a new sheet
  2. name it ‘variables’
  3. make three columns: name, value, description

Now you can fill the table you just made with all kinds of settings, constants or calculations that you want to use in your elaborate VBA program.

I am a lazy teacher, and I have a lot of students, so I work a lot with short macros that can help me get more spare time. Here’s an example:

    For Each subfolder In FSfolder.SubFolders
        If subfolder Like "Student *" Then
            [studentNr] = Right(subfolder, 6)
            If Not FileExists([sClass]) Then
                FS.createFolder [sClass]
            End If
            FS.MoveFolder subfolder, [newFolder]
        End If
    Next subfolder

In line 3 I fill the named range ‘studentNr’ with a value taken from a folder, which contains a student number. In the next line, I check if a folder for that students’ class has already been made, and if not, I make the folder. The variable [sClass] does not exist in my code. It only exists in my variable table in Excel. The cell contains a VLookup function to find in which class this student is currently enrolled. Similarly I have a [Teacher] variable, also with a VLookup function. NewFolder is simply a concatenation and formatting, which is also easily done in Excel.

As you can see, the values for [sClass], [Teacher] and [newFolder] are filled automatically by Excel, and I don’t have to process anything.

Programming like this in Excel is a new paradigm. You don’t churn out all your code top-to-bottom as you used to. You create sheets with lots of calculations, lookups etc, and then you make a tiny program that links all this together. Excel can do some crazy fast, complex stuff, and you should never have to program those anymore!

Crazy Caveats

Well wasn’t that incredible? You may not be used to programming like this, and I recommend this method only for experts. You should be in full control of the worksheets, or otherwise someone will mess with your program. Also, when you are part of a team, you should make sure this ‘magic’ is elaborately documented in the code (e.g. in a header of the function mention which Excel named ranges are used).

Good luck.

Learn more: FastExcel Blog

 

The selection contains multiple data values… Merging cells in Excel.

This is a super powerfeature I have always missed in Excel: the ability to join the content of cells without losing any data. Sorry, what’s that?? Yes, I know about the ‘merge’ option. It’s lame! Try merging two cells that both have content. Excel answers, delightfully happy, that you will only keep data in the first cell and lose all of the data of the other cells, and go deal with it.

multiple-data
multiple-data

So, I decided to ‘deal with it’ and I present here several macros for your pleasure and entertainment that will solve this once and for all.

Merging, Joining and Combining

Well, that’s all the same of course. Let’s start with a simple macro to join a bunch of cells and put the result in the first cell.

Sub Join()
   Dim out As String
   Dim c As Range

   out = ""
   For Each c In Selection.Cells
      out = out & c.Value
      c.ClearContents
   Next
   Selection.Cells(1, 1).Value = out
End Sub

How does it work? First, we define the variable out which will contain all content. Then we loop over all cells in the current selection and we combine their values using the concatenation operator “&”. Finally we put the value of out in the first cell of the selection.

If you have never created a macro before, you can add them by pressing ALT+F11. After that, make sure to copy this one to the personal macro workbook.

In some occasions I needed to merge, but also keep the data separated by either commas or newlines. So I made a slightly modified version with arguments. Note for noobs: you can’t run these like normal macros, you’d have to go into the VB editor and start them from the immediate window. Or, you can make a short calling-macro similar to the JoinWithBreaks below.

' join the contents of a selection of cells
' and put all of these together in one single cell
Sub Join(Optional s As String = "", Optional wrap As Boolean = True)
    Dim out As String
    Dim c As Range
    Dim n As Integer

    out = ""
    For Each c In Selection.Cells
        n = n + 1
        out = out & c.Value
        If n < Selection.Cells.Count Then
            out = out & s
        End If
        c.ClearContents
    Next
    Selection.Cells(1, 1).Value = out
    Selection.Cells(1, 1).WrapText = wrap
End Sub

Sub JoinWithBreaks()
    Join vbNewLine, True
End Sub

Fillerup

Another problem closely related to the merge problem is when you have a sheet looking like this:

excel fill problem
excel fill problem

And let’s say it extends a long long way to the bottom for your archive of ten years. To create a usable Excel table and e.g. to create a pivot table your table needs to have values in every cell, not just the first one. Humans can reason that cell A3 also belongs to January, but computers and thus Excel cannot. Clicking on the autofill handle in cell A2 fixes this, but doing so for 100 cells is still very tedious work. For that, we can use the following macro, which will automagically fill every empty cell with the value right above it.

Sub Filler()
    Dim c As Range

    For Each c In Selection.Cells
        If IsEmpty(c) And c.Row <> 1 Then
            c.FormulaR1C1 = c.Offset(-1, 0).FormulaR1C1
        End If
    Next
End Sub

Cybernetic Programming

cybernetics

I’d like to make a proposal for a new kind of programming (at least new to me, LOL). When I was studying back in the nineties I became interested in something called ‘sociocratic organization‘. This is a form of organization based on Cybernetics. Cybernetics is the study of systems with feedback loops. When you think of a thermostat in the room, it has a feedback loop. When it gets too hot, it cools, when it gets too cool, it warms up, and that way it keeps the system in balance. In computer programming we don’t deploy such features normally. We pray that all goes well, and that we didn’t forget anything, and if it does go wrong, the whole system usually crumbles apart. In web programming that’s usually a minor glitch, since just one page of our whole system breaks. In desktop computing it’s much worse. And in embedded programming it’s disastrous. Now instead of writing numerous unit tests, or mathematically proving our program, perhaps it would be possible to use this idea of a feedback loop in many aspects of our programming.

Consider you have to write a program that is to determine the best results for a search, similar to Google (but not as vast, so let’s say a small web site). We need to display what’s relevant to the user, but we don’t know what is relevant. So you just do a simple text search to find a record within a table. Now you check which of the results is clicked for this particular search result, and then you log the search term, and with it the clicked result.

Then a new user shows up and does the same search, and clicks the same result. You increment the counter for this result on this search in the log. After a while we should see our statistics in the table represent what people were searching for, and which records were ‘successful’ to them (at least they clicked it). Depending on these results you can now decide to alter the order of the search results, where the highest scoring record is put on top of the search results page.

Pretty nifty. I Googled it and found that Microsoft Research has written a paper on something similar. So expect this to Bing us soon.

Convert Excel date value to SQL date

When creating SQL statements you’ll often need a date in the ISO 8601 standard format (e.g. 2010-03-26 12:34).

Of course you can change the format in Excel to show it as such, but that doesn’t give you the string you need, e.g. in an insert or update statement.

Here’s an Excel function to make an SQL date value, presuming the date value is in cell A1:

=TEXT(A1,”yyyy-mm-dd hh:MM:ss”)

This circumvents the use of complicated IF and date/time functions. Append a “Z” if you need to indicate the timezone as UTC (i.e. GMT) time.

Here’s a short VBA function to create this type of date

Function SQLDate(d)
SQLDate = WorksheetFunction.Text(d, "yyyy-mm-dd hh:MM:ss")
End Function

Put this code in a new module in your workbook to instantly start using the function in Excel like this: “=SQLDate(A1)”

SQL date in Excel
SQL date in Excel

More information:

http://en.wikipedia.org/wiki/ISO_8601

A dead simple, lightweight javascript calendar

calendar-size-badgeI recently needed a good light weight javascript calendar but couldn’t find it. The problem is that online I could find many calendars, but they all suffered from one of two problems: they were either too heavy, or they were badly written (aka coding like it’s 1999).

I hereby present a very lightweight javascript calendar object. It can be used anywhere where you need to select a date.

It features the following:

  • no library. That’s right, it does NOT use mootools, it does NOT use jQuery.
  • clean code. Yes it can be improved, but that would make it a heavy calendar again.
  • it does not do Date magic. If you want date magic I can recommend date.js. (oh, and that means things like ‘3 days ago’ etc.)
  • it’s lightweight, mainly because it doesn’t do magic and it doesn’t have a library. It’s 2K uncompressed, 1.5K using dean edwards packer.
  • no popup windows. All

Download

Download mbcalendar 1.0

Usage

var cal = new MBCalendar('inp', 'btn', 'click');

Where

  • inp is the id of the input that will be retrieving the calendar, and
  • btn is the id of the element that will trigger the calendar to show, and
  • click is the name of the event that will trigger the calendar
  • in the onload script, in this example ‘out’ is the id of the element that will hold the calendar.

This way it can be used as a date picker, but it can also be used as a visible calendar with navigation (static). Basically the output is simply the HTML, so you can use it anywhere.

All dates van be individually styled, since they all get a unique id. Also all dates get class names so you can style either a certain year, month, or day of the month. E.g. ‘.y2009’ will style every date in 2009, ‘.m3’ will style every march, and ‘.y2009.m3’ will style every day in March, 2009.

Source code

Now for the code

/*(c) Michiel van der Blonk 2009 - license: http://www.opensource.org/licenses/mit-license.php*/

function MBCalendar(m, y)
{
this.m = m;
this.y = y;
this.weekDays = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
}

MBCalendar.prototype.$ =  function(s) {return document.getElementById(s)};

// export as array
MBCalendar.prototype.toArray = function() {
var d;
var dates = [];
for (var i=1;i<32;i++)
{
d = new Date(this.y,this.m-1, i);
if (d.getMonth() == this.m-1)
dates.push(d);
}
return dates;
};

// export as html
MBCalendar.prototype.toHTML = function() {
var i;
var ret, dayId, dayClass;
ret = dayId = dayClass = '';
var dates = this.toArray();
ret += '
<table class="cal">' + '
<tr>';
for (i in [0,1,2,3,4,5,6])
ret += '
<th>' + this.weekDays[parseInt(dates[i].getDay())].substr(0,1) + '</th>
';
ret += '</tr>
<tr>';
for (i in dates)
{
var d = dates[i];
if ((parseInt(i) % 7) == 0)
ret += '</tr>
';
if ((parseInt(i)+1 % 7==0) && i<dates .length)
ret += '
<tr>';
dayClass = 'y'+d.getFullYear() + ' m' +(d.getMonth()+1) + ' d' + d.getDate();
dayId = 'day-' + parseInt(d.getTime()/86400000);
ret += '
<td id="' + dayId + '" class="' + dayClass +'">' + dates[i].getDate() + '</td>
';
}
ret = ret + '</dates></table>
';
return ret;
};

window.onload = function() {
var $ = function(s) {return document.getElementById(s)};
var c;
$('showCal').onclick = function() {
var y = $('year').value;
var m = $('month').value;
c = new MBCalendar(m, y);
$('out').innerHTML = c.toHTML();
};
$('prev').onclick = function() {
var d = new Date(c.y,c.m-2,1);
c = new MBCalendar(d.getMonth()+1, d.getFullYear());
$('out').innerHTML = c.toHTML();
}
$('next').onclick = function() {
var d = new Date(c.y,c.m,1);
c = new MBCalendar(d.getMonth()+1, d.getFullYear());
$('out').innerHTML = c.toHTML();
}
};

If you like you can integrate the prev, next and show methods in the Calendar object itself of course. I invite all javascript experts to crunch the code even more, without making it unreadable!

If you don’t integrate that code, you will need to add some standard HTML in a page to get a functional Calendar demonstration:

<input type="text" id="year" value="" />
<input type="text" id="month" value="" />
<button type="button" id="showCal">Show Calendar</button>
<button type="button" id="prev">prev</button>
<button type="button" id="next">next</button>

Some alternative calendars you might like:

update: there was still some debug code in there, it’s removed now. Also it seems wordpress messes up the code when I paste it. Change line 16 to have the ‘<‘ char instead of &lt;

How to shuffle an array

This is a very common programming problem, e.g. when you wish to show some images in random order, when you want to show a random quote, etc. I will show you a solution in JavaScript, but it can be ported to other languages easily.

The common solution to shuffling is to swap random elements, but swapping means you have to constantly work with two elements, and it can be done by using only one. Also, it’s common to randomly swap e.g. 1000 elements, but that wouldn’t work well for very large arrays.

All you have to do is follow a couple of simple steps:

  1. First, of course you start with a simple array, which you have in a specified order.
  2. You place a random element in the output array, and remove the element
  3. Repeat step 2 until the array is empty

Here’s the full code

function shuffle(r) {
	var pos;
	var out = [];
	while (r.length > 0)	{
		pos = parseInt(Math.random()*r.length);
		out.push(r[pos]);
		r = r.slice(0,pos).concat(r.slice(pos+1, r.length));
	}
	return out;
}

Let’s examine the parts. First, the variables pos and out are defined, and out is initialized to an empty array.

	var pos;
	var out = [];

No we “loop” through the array. But, do note that this is not a real loop, in fact we’re constantly going to remove elements until the array is empty. So the simple check on length is enough here.

	while (r.length > 0)	{

We find a random element. For large arrays the Math.random() method can be considered unreliable, but then I mean really large.

		pos = parseInt(Math.random()*r.length);

Next, we add the random element to the output array, using push. Then we remove the element from the original array. This is done in three parts:

  1. get everything on the left of the element
  2. get everything on the right of the element
  3. concat these two arrays to form a new array

That is a really difficult way of removing just one element, but deleting an element from an array is not a native javascript method. John Resig wrote another version of delete

		out.push(r[pos]);
		r = r.slice(0,pos).concat(r.slice(pos+1, r.length));

And last but not least we return the new array:

	}
	return out;
}

Example

Here’s an example of calling the function, shuffling 52 integers

var r=[];
i=52;
while (i--)
	r.push(i);
alert(shuffle(r).join(','));

Extending Array

If you feel so inclined you can make it an extension of the Array object in javascript, like so:

Array.prototype.shuffle = function() {
	var r=this;
	... //rest of code
}

Note the additional "r=this" line.

Drawbacks

There are some drawbacks and warnings to take note of:

  • It can be slow for huge arrays, I would recommend it for arrays under 1000 items
  • It takes up additional space, since it creates a new array
  • The delete can be improved, by using e.g. John Resig’s version

A shorter ‘hack’

The following is a short hack that can most definitely suit one time needs. It’s a form of sorting randomly, which sounds weird, and it is, but it works. However, if the random value is not seeded again, the next time you run it you end up with the exact same sequence.

function shuffle(r) {
  r.sort(function() { return Math.random() } );
}

For more background see e.g. the Fisher Yates shuffle method (which by the way this is not, but this is).