Archive for the ‘Programming’ Category
Scribbler is a WordPress plugin that allows you to show an animation of handwritten text on your site. See an example on deschrijfcoacharuba.com
Why use Scribbler?
Handwriting is very personal, this can give a personal touch to your logo, your site, product, app or videos.
How to use Scribbler
- Download the plugin (we’re not on the WordPress directory yet).
- Go to your admin section in WordPress and choose Plugins.
- Choose ‘upload plugin’
- Press ‘choose file’ and then install
- Alternatively, unzip the plugin. Use FTP to go to your WordPress/wp-content/plugins folder and upload the directory there. Activate the Scribbler plugin
- Go to Settings > Scribbler
- Enter the text you’d like to see animated on your home page
There is currently only one option: text. This defines what text to show on the page.
You will probably want to customize the CSS file, which is in the same folder as the plugin. By default the text appears in div#pen, which will be placed right below the content of the post.
- The plugin is programmed to only work on a home page (using is_frontpage), because having a handwriting animation on every page gives bad usability.
- The speed is fixed to 400 ms per character
- As already said, it has only one font: ‘Tangerine’ (a Google Font).
There is only one font (Tangerine). If you want to use another font, you’ll have to do the preparations yourself: create a cufonized font and modify the default in Scribbler.php.
There are no known problems as of yet.
If you want to help me, I’d appreciate to be sent cufonized fonts. I will then implement the use of that font in the plugin.
I want this too on my site!
If you’re not technically inclined I can customize it for you on your site. Just leave a comment or contact me (http://about.me/michiel) and we can agree on a price.
The back story
Recently I have been trying to limit my use of VBA. It’s not that I was addicted and needed to go to VBA-rehab, on the contrary, I still love VBA till death do us part. But the thing is, every time someone opens an Excel sheet with VBA macros they are reminded of Excel’s vulnerability and the risks of macros.
So I set out to make workbooks that do the same thing, but without VBA. Not always is this possible, or efficient to do so. But when it’s possible, it also comes with great performance and great stability. No code needs to be changed, ever. Of course there are also downsides. It’s not as flexible as VBA, so you’re stuck in a rigid framework that solves one thing and one thing only. But it does it so well, oh my.
My latest endeavor was with permutations. I needed something that would generate all permutations of the tokens F,C,R,A (don’t ask) with repetition. As some know I am an avid speed cuber, that is solving the Rubik’s cube for speed. And the Rubik’s cube is a permutation puzzle. So I have dealt with permutations quite a lot. For those who haven’t paid attention in math class: you should know that permutations come in two flavors: with repetition (Pr) and without repetition (P). The number of permutations (Pr) in these four tokens F,C,R,A is 4^4, or 256. That number is exponential, so it grows so fast that at 5 tokens you are at 3,125 permutations and at 6 tokens at 46,656 permutations. At a set size of 10 tokens you are at 10^10 or 10,000,000,000 (ten billion) permutations. VBA would surely choke on that number of statements to follow. Excel can handle 1 million rows, although I wouldn’t put it to the test with that.
A clean slate
I started out writing a VBA program, that generates all permutations (Pr). The funny thing is if you go online and expect to find a bunch of worked out examples of algorithms, you don’t. Almost all examples you find are about permutations without repetition, which is like working with real objects, since you can’t duplicate real objects. The lottery is a good example of this. The program worked, but it was slow, and cumbersome. So let’s drop VBA and try it without.
First, I set out with some settings (sheet ‘settings’). We define the pattern, and calculate the size of the set (using LEN), and the number of possible permutations (using Length^Length). These values will be used extensively in the functions to generate permutations.
The maximum length of the pattern is set to 5, which totals 3,125 permutations, an amount which Excel can handle in the blink of an eye. You could extend the grid to a width of 7, which would come to 823,543 permutations. I’d be interested to know how fast Excel would generate the output, and how big the file would become. If you try it out please let me know.
Go to the output sheet and look at the grid on the right. There you see in the top row a couple of simple formulas. They are to set the repeat cycle of that column. In the first column you see a token repeated once, in the second column 4 times, in the third column 16 times. We’re multiplying by Length to set the repeat cycle. This is an easy way to generate permutations of any set. Think of how it works with regular counting. You start with 0,1,2,3,4,5,6,7,8,9, and then go one digit to the left, and you repeat, but now in cycles of 10, so that 10,11,12,13,14,15,16,17,18,19 has exactly 10 times a 1. We’re using the same principle.
Our basic generating formula is as follows:
If you don’t know MOD, this is a function for modulo, also called the remainder after performing division. The MID function gets a character from a specified position in the string. The column with N is simply to count and use the MOD function properly.
By using absolute referencing we are now able to copy the cell E4 to all other cells in the grid, while keeping a properly working formula. With a pattern of length 4 we can ignore the last column, which is only needed for a pattern of length 5.
In the settings sheet you will see a width and a height. By selecting the range starting at E4 with that width and height, we get exactly all permutations in the set.
Alternatively, on the left, there is a table with all tokens concatenated in one string, for ease of use. The formula for this is:
Using FCRA as a pattern we can now see all 256 permutations in column A!
Well, permutations without repetition are actually a subset of permutations with repetition (P < Pr). In a permutation without repetition you don’t have any duplicates. So for the tokens F,C,R,A a valid Pr would be FFFF, but it’s not a member of P. You can only get a member of P by swapping original tokens. So e.g. FRCA is a member of P. That’s why the number of items in P doesn’t grow as fast as in Pr. Four tokens gets to 4*3*2*1=24 permutations. This is called a factorial.
If a token set does not contain duplicates we can easily filter out the permutations we need. E.g. in the set A,A,B,B we still get duplicates in the list, and so we can’t filter. But in F,C,R,A it’s quite possible using Excel. The formula used is a but difficult though, and requires some thought:
=SUM(IF(FREQUENCY( MATCH(OFFSET(E4,0,0,1,Length),OFFSET(E4,0,0,1,Length),0), MATCH(OFFSET(E4,0,0,1,Length),OFFSET(E4,0,0,1,Length),0)) >0,1))=Length
Here, OFFSET gives us a dynamically defined range, which is handy, because we don’t know how long the pattern is beforehand. In this formula, Length is the size of the set (aka the length of the string). Both OFFSET and MATCH return multiple values, so it’s impossible to split the formula into more cells, but just for clarity, let’s view it in condensed form:
What it does is it totals the frequencies of each character in the set, so in this case it returns 2, and then checks to see if it matches the length (4). If it matches we have a permutation. Note this only works for patterns that have no repeating tokens, like FCRA.
Using named formulas we can simplify the long formula to:
=SUM(IF(FREQUENCY( MATCH(tokens,tokens,0), MATCH(tokens,tokens,0))>0,1))=Length
…where tokens equals OFFSET(E4,0,0,1,Length)
Now we have a formula to detect permutations. Unfortunately we still have duplicates, because our table always has 5 tokens and we might have a shorter pattern, like our example FCRA. So we use an IF to detect empty cells in column A and we can now use Excel’s filter (On the ribbon choose Data, then Filter) to get all permutations.
permutations with repetition (320KB)
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.
- make a new sheet
- name it ‘variables’
- 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!
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).
Learn more: FastExcel Blog
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.
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
Another problem closely related to the merge problem is when you have a sheet looking like this:
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
One of the hardest topics in school next to computer science is physics. Time and again I see students struggle with the topics, and I have to admit I didn’t find it easy in high school myself. So I started studying physics again using Khan Academy, and gained much insight into the basics. I have admired Sal Khan since he started his site, and as a school teacher I also think I should help him, and my students, wherever possible.
I started drawing a diagram of how all things in physics connected to each other, and while doing that, I realized there should be software to do this better. After a short search I found the wonderful D3 toolkit, based on web standards, just as I like it. D3 can create awesome diagrams, graphs, infographics, you name it.
So now I have for your pleasure and learning aid a physics based overview of common physics quantities. Click the image to see the actual animated model.
Some tips on how to use it:
- Click the diagram to automatically redraw it
- Drag nodes to change the layout
- Mouseover a node to see related info
Use in physics assignments
The colors in the diagram denote related quantities:
- blue – essential axiomatic units m, l, t
- green – related to movement
- orange – related to energy
- red – related to a single unit
To use this diagram in your physics assignment, look at the known variables. Find those in the diagram. Now see if you can combine those to make more variables: follow the arrows. If you can start from two known values you can usually calculate a third. You continue doing this until you have found the desired outcome.
When we write for the web we most certainly write for the fast paced audience that has no time to read pages and pages of text. That’s a given, and it’s what sets it apart from writing for paper. The other characteristic is links: if you need to explain something, it’s easier to just link to wikipedia than to insert the actual text. Also you don’t want to infringe on copyright.
Let’s look at our goals for writing for the web:
- Make it easy to read
- Make it easy to use
- Make it easy to find (search engine position or ranking)
- Make it sell (in case you’re selling, but you’re always selling ideas)
- Make it look good
Those goals are also connected to viewpoints from certain people: the writer, the usability expert, the SEO specialist, the sales representative and the designer. These fantastic four have to make sure the page adheres to all their standards. And here is where it starts to itch:
- easy to read: plain fonts, short sentences, short text, clear message, bold, pictures and diagrams that explain difficult topics.
- easy to use: minimal use of text, clear and large buttons, minimal use of design elements
- easy to find: clear headings, short sentences, keywords in bold (not the same ones), no pictures required.
- sell: everything leads to a buy, no navigation on cart page
- look good: creative fonts, no bold text, plenty of non-illustrative pictures (preferably photographs) and design elements
In my opinion you should work from the outside in: make it look good, then make it easy to use, easy to read, easy to find and easy to sell, in that order. But as you make it look good (design process) you can of course have some consideration for the other aspects. It should also be noted that SEO is still voodoo, since nobody knows how Google really works. Also, search engines, in particular Google, change their ways of working constantly and they get better and better at identifying your pages. So don’t try to fool them, it’s not worth it.
There will probably always be a battle between usability experts and designers, because their worlds are so far apart. But to a modern web user something like useit.com looks like it was made 20 years ago and doesn’t instill trust in a user, something that is vital to sales.
Remember I may not be an expert in all those areas, but the ideas presented do come from the leading experts in these areas.
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.
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:
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)”
There are tons of ways to remove duplicates from a list of items, most of which are way too complicated and technical for a noob (if you don’t know what this is, then that’s you) to perform.
Let’s look at some of them, and let me know in the comments if you think these were useful. Here they come, in order of increasing difficulty (geekness)
TextPad is a free (well, nagware) text editor with so many built in tools I cannot talk about it without crying…
- open the file in TextPad
- select Tools > Sort
- check the box at ‘remove duplicate lines’
- click OK
Another program abused by millions (billions?) to do stuff that could be done with a 10 year old cellphone. What the bozos at Microsoft tell you is the dumbest way to do it, because you’re overwriting your original list. Here’s the smart way
Select the data
- Click Data > PivotTable… (Office 2003) or Insert > PivotTable (Office 2007)
- Click ‘Finish’ to create a new sheet with an empty pivot table
- Drag the column for which you need to remove duplicates into the left part of the pivottable
- adding flavor: you can now sort, filter, group, analyze, you name it.
If your data is in a database, and you have access to SQL, perform the following query:
- SELECT DISTINCT(column) FROM table
- in place of ‘column’ you type what you need to be unique
- in place of ‘table’ you type the table name
- tip: you can combine more columns by typing (column1, column2, …)
If you have the file on a linux or unix system, from the terminal (command line) type
- sort -u file > output
- where ‘file’ is the name of your file and ‘output’ is the name of the output file.
I’d like to know if you can come up with new and maybe even faster ways!
I have worked for almost 10 years in web development now, and here’s what I have learned from the clients: you can ruin your website too!
1. Get the cheapest designer you can get. If your nephew says he can do it, let him do it. It’s not that hard.
2. That being said, you can also do it yourself. Why pay for some fancy designer when you can buy or get a template. And HTML+CSS is easy, right?
3. Even cheaper, copy someone else’s site and just change the text.
4. Don’t start a forum or community. There are a lot of people out there ready to say nasty things about you.
5. You should get a CMS if possible. Then you can control everything yourself real easy, and choose any font and color. Did you know with a CMS you can just copy straight from MS Word? You can make sure people notice your links by using a big red font.
6. Don’t listen to any advice from your designer. They’re just after your money, not to help you.
7. If it looks good in Internet Explorer, then you’re done. There are so many browsers there’s no keeping up with it, and most people use IE anyway.
8. Almost everyone has broadband, so keep your images in high quality. Browsers resize them anyway when they display them.
9. Tables are cool. With tables you can get layouts done that are really hard without them.
10. Get as much information from your visitors as possible. When they sign up, ask for name, gender, location, email, interests. You can even sell that information later and make a good profit.
And all of the above will help you to get the lowest possible position on Google. But there are companies that can help you to become #1, just wait for the ‘offers’ to come in.