Category Visual Basic

[HW 4.3b] Spreadsheets as translation engines

Part of learning a language involves organizing and categorizing large numbers of vocabulary words.  When I was a Middlebury Student, I remember using Excel to create lists of vocabulary words for my French class.   Here’s a workbook that I put together, including the VBA code, that reaches out to the web and pulls down […]

VBA for Google Spreadsheets: JavaScript

Visual Basic is a Microsoft product. So, it should come as no surprise that Google Sheets do not support Visual Basic. Instead, Google Sheets support JavaScript. If you’re interested in this area, you may want to check out this sample book, or buy the full book which offers examples of how to use JavaScript to write custom […]

[HW 2.5d] Identifying and Counting Unique Items

In working with lists, it’s very common to have to produce a unique set of items in a range.  This might be useful for summarizing the dataset.  Or, it might be important for reconciling the dataset against some other list.  Or maybe you just need to check for duplicates. Another aspect of this problem is to […]

[HW 2.1b] Making It Easier to Sort

In Excel, there are a few ways you can sort a data set.  You can: Click on Data/Sort then navigate the Sort Data dialog box. Right click on a highlighted a data set and hit sort. Apply a filter to a data set, then click on the filter arrows, and then select “Sort A to […]

Secret: Formula Auditing as a Means of Navigating a Model

When people watch me work, they are sometimes surprised that I tend not to use the mouse.  I find that clicking on things takes time.  It slows me down.  And I find switching back and forth from keyboarding to mousing is disruptive.  So, long ago, I chose to invest the extra time in learning the keystrokes shortcuts […]

A note on file formats

Excel offers multiple file formats. Pre-2007, all excel files were saved as .xls files. Since 2007, there are a few other options, each with its own cost/benefit. The nice people at AMT Training prepared the below chart.  I’ve shown the first three file types.  There are others, but they are less common. File Formats File extension […]

Resources for Learning Visual Basic

Here are a few resources from Microsoft on learning Visual Basic, courtesy of the nice people at MSDN, which stands for Microsoft Developer Network. User Defined Functions and Subs Sub Statement Function Statement Data Types Data Types DIM Statement Option Explicit Built in VBA Functions String Functions Math Functions Comparison Operators Statements If Then Else […]

Primer on Visual Basic

Gaining a moderate understanding of Visual Basic is an important goal for this course.  However, I’ve not assigned a textbook to work from. There are many, many books on the subject and Amazon would be delighted to sell them to you.  However, I think there is something a little strange about learning how to program from […]