The Lightbulb Audit – Ver. 1.2.2

Greetings all,

It’s been a while since I posted anything substantial, so I’m glad to finally be able to announce an update on The Lightbulb Audit.

For those of you who aren’t familiar with it, The Lightbulb Audit is an app designed to help you take stock of not only the types of light fittings in your house, but also what type of bulbs you have installed. This will hopefully assist you in reducing your electricity consumption (through being able to audit your usage and demonstrating potential energy savings). It will also hopefully ensure that you know what type of bulb you actually need, the next time you’re trying to replace that blown light that you have been putting off because you can never remember whether it’s a screw or bayonette type, and how bright does it need to be, besides, going to the toilet in the dark isn’t THAT bad, right?

Refer to my original post if you want a more rambling details: The Lightbulb Audit – original post

To get the App (android only, and I’m not going to apologise for that): The Lightbulb Audit – via Google Play Store

Updates in this post include:

  • Finally implimented a Sort function (sort by room, type, power, or order added to list)
  • Import function (import from .csv in the same format as the export .csv, including header row. this is for importing from the excel version of the-lightbulb-audit)
  • Minor formatting and usability update
  • (Minor bug fix from versions 1.2.0 and 1.2.1 which I released in rapid succession last night… oops!)

As I’ve said before, two very important points:

  • I’m not a professional developer, so if you have any negative feedback, comments or questions, please leave them as comments HERE, rather than trashing the App’s review score
  • I’ve posted the App for free, but there is a small ad banner in the app. The only way I’ll ever make any money off this app is if you CLICK ON THE BANNER when you see an ad for something that might interest you. I’d never encourage you to click on ads just to get me a few cents, but if you’re in the app, and you see an ad for something that tweaks your interest, check it out!

Thanks for your time, I hope you find my app useful!

The Lightbulb Audit

For the past few months, I’ve been devoting a fair chunk of my “free time” (which is to say, very little time at all… I have a baby and a toddler, neither of whom sleep) to learning a bit of Java. Specifically, Java for Android. This has been… less fulfilling than expected. I’ve learnt a fair bit since the middle of the year, but not as much as I’d hoped. I do a little recreational programming in a few different languages, mostly Visual Basic and C++ derivatives, and while all languages and coding environments have their challenges, none of them compare the the peculiarities of android app development. I won’t wax poetic here, because this post isn’t actually about Java; this post is about results.

Finally, I have got my first Android app ready for public consumption (which is to say, I lowered my standards until they matched where my app was at). I think it’s pretty reasonable, in terms of presentation, but the important thing is that it’s useful. And that pretty much sums up my standards for anything.

The app is a light bulb tracking program, which I affectionately call The Lightbulb Audit. This app is designed to be a list of every light fitting in your house, and every bulb installed in those fittings, which can be carried in your pocket. That way, the next time you see LED lightbulbs on sale, instead of thinking to yourself: “Gee, that seems cheap. I wonder if I actually need more lightbulbs?”, you will check your phone and say “Wow, I actually have three blown lightbulbs that I haven’t replaced yet, no spares, that one dim bulb in the bathroom that really needs to be replaced with something more powerful, and most of my bulbs are ye-olde incandescent lightbulbs. What are we savages? time to upgrade!”… or something very much along those lines.

So, here is a link to my app on the Google Play store (sorry Apple users, after the experience I just had learning to program for Android, it’s unlikely I’ll get to you any time soon. Also, I don’t have an iPhone to test on. Also: *sound of me blowing a raspberry at you*):

https://play.google.com/store/apps/details?id=teslaandi.wordpress.com.lightbulbaudit

(Please see “important” note below!)

I appreciate feedback, comments, suggestions, and bug reports, so please feel free to leave comment on this blog post to let me know what’s on your mind (regarding the app).

But! Because I’m such a nice guy, I’ve also put up a copy of my Lightbulb Audit spreadsheet (which is how this thing originally started life). Not only can non-android users access this, but you can also export a CSV file from the app and paste it into this spreadsheet:

the-lightbulb-audit

Please, feel free to modify the spreadsheet, add colourful graphs, add features, etc. And please share your updated version if you do! Sharing is caring.

IMPORTANT!!!! – Because I’m such a nice guy and put my app up for free, I don’t make money off it just from you downloading and using it. Where I can make a few cents is from you clicking on the ad banner at the bottom of the app (which I’ve tried to make as unobtrusive as possible, because the afore mentioned niceness). So, if you see any ads that are even vaguely interesting: please, click on it. At the time of writing, my revenue is up to about $2.06… sigh… Oh well, I do it because I enjoy it, not to make money. Also because I’m just such a nice guy and want to help you and your lightbulb situation… Which is a mess, right? Be honest.

ALSO IMPORTANT!!!! – Please don’t leave poor reviews. Any comments, questions, queries, or suggestions: Please leave a comment here, and I’ll do my darndest to look after you…

…On account of being such a nice guy.

Excel Functions That Should Exist, and Making Your Own

Maybe other engineers have had different experiences, but I know that during my degree, Microsoft Excel became a very close and special friend to me. Sure, it’s not the most powerful software, but it can do any calculation that you could do on paper, and it will do it over and over and over again. This makes it great for iterative calculations.

God I love a good spreadsheet.

The problem with Excel is that, like most good pieces of software, it was written by a person. This means that it was written by exactly the kind of jerk who would deliberately and maliciously write the code in a way that slightly inconveniences me. Those guys are the worst.

Okay, maybe I’m exagerating. But there are certain functions in excel that aren’t quite right, or haven’t been followed through to their logical conclusion (although I’ll touch on the likely reasons for this in a moment). This post is about addressing these shortcomings, and writing functions that will work the way you want them to.

Writing User Defined Functions (UDF):

To write your UDF, hit   Alt + F11   to bring up the VBA (Visual Basic for Applications) window. Select a location to store the function. Your options are as follows:

  1. Worksheet
  2. Workbook
  3. A Module

locations for VBA code

Simply double click on the worksheet, workbook, or Module in which you want to insert the code. If you want to use a module, simple click INSERT – MODULE, as shown below. You can rename these modules whatever you want, and export them for use in other spreadsheets. I like modules, as they are easy ways of tidying up your code library within a spreadsheet

insert module

Note: Ensure that you are writing your code inside the right VBA Project. You’ll see in the panel on the left of the VBA window  that there is a VBA project for each spreadsheet open, as well as any Add In spreadsheets running in the background. So just select the worksheet, workbook or module within the appropriate project.

Now, the fun bit: Coding.

Simply start writing your function using the following:

Function Functionname(value1, value2, value3)

You can specify the variable type for the values given in brackets, e.g. value1 As Range, or you can leave it implicit.

Excel should completed the End Function statement for you, and you can start writing your code in between these statements:

functionResult=value1+value2+value3

Within your function, simply set the output value by using a line similar to:

Functionname = functionResult

Putting it all together, in this example, you are left with your User Defined Function, which should look something like this:

Function Functionname(value1, value2, value3)
functionResult = value1 + value2 + value3
Functionname = functionResult
End Function

To use this function just type in “=Functionname(1,2,5)” in a cell, to return the value “8”. You can refer to a cell for any (or all) of the inputs, and it will use the value inside that cell.

Once you’ve written your function, if it’s one that you’ll use again and again, you can copy and paste it into the VBA project for any new spreadsheet that you need to use it in. Alternatively, if you only need to use these spreadsheets on YOUR computer (or if you want the code stored somewhere handy for easy pasting into new spreadsheets without having to find the original), you can post the code into a module in a blank spreadsheet, and save that spreadsheet as an Add In (.XLAM format) (see image below), then activate the add-in. Once you’ve done this, any time you open up Excel your computer will open up the XLAM file and make any SUBs or FUNCTIONs in it available to you. (Note: if you send your spreadsheet to someone who doesn’t have your add-in, it won’t operate correctly as the code isn’t availble, so in that case it’s good to save the function in the WORKBOOK of that specific spreadsheet). I’ll do another post shortly about how to add it to the list of functions displayed when you pres the “fx” button, as well as how to show descriptions for the inputs of the function.

saving Excel Addin

As an example of where these custom Add Ins are useful, I have add-ins that I use at work for quick “back of the envelope” calculations (e.g. “how much power would the conveyor need to lift 20m?” or “what would the full load torque of that motor be?”), and since I generally have Excel open, I can simply use my LiftPower() or FLT() functions in excel to get a quick answer.

Example: Lookup

I’m sure many of you are familiar with the shortcomings of the excel Lookup functions (LOOKUP, VLOOKUP, HLOOKUP). Primarily these shortcomings are:

  • Formatting limitations (the lookup column must be sorted in ascending order)
  • Result limitations (minimal control over what to do if no exact match is found, or if multiple matches are found)

After looking at how these functions work, it’s obvious that the code was written so as to give a reasonably workable function, while minimising processing requirements. Since excel spreadsheets can get quite large, an older computer could get quite bogged down in running the multitude of comparisons required by the Lookup function, so it seems like they let it shortcut out of the code by exiting the loop and returning the result value as soon as the value in the next lookup cell is greater than the value being searched for. Presumably the decision was made that it was less likely that people would be bothered by having to sort their data in ascending order (for the lookup column) than they would be by the slowness caused by the spreadsheet running through excess code.

As for shortcomings in the outputs, this is a result of an assumed philosophy of keeping things simple. An increased number of options just increases the difficulty of using the function.

At the end of the day, anyone who is so into Excel that they aren’t entirely happy with how these functions operate, is probably into it enough to write their own User Defined Functions.

Thank the heavens for VBA. Can I get an “Amen!”?

In a fit of pique, I wrote my own lookup function, which I call Ulookup (Ultra Lookup). Depending on the option selected, the function will search the Lookup array and return one of the following types of result from the Results array:

  1. Get first exact value
  2. Get last exact value
  3. Get nearest value
  4. Get nearest value below
  5. Get nearest value over
  6. Return “” if not found
  7. Interpolate between two nearest results
  8. Sum of exact results

Here’s the code for my Ulookup (I’ll upload a link to the actual XLAM format add-in as an example, just as soon as I find somewhere to store it):

Function Ulookup(value, lookupArray As Range, resultsArray As Range, lookupType As Integer)

temp = “error”

Dim temp1, temp2, a, b, Ra, Rb As Long
Dim check As Boolean
check = False

Dim Rl, Cl, Rr, Cr, i As Integer

Rl = lookupArray.Rows.Count
Cl = lookupArray.Columns.Count
Rr = resultsArray.Rows.Count
Cr = resultsArray.Columns.Count

‘ lookupType is type of lookup. 0 = get first exact value, 1 = get last exact value, 2 = get nearest value, 3 = get nearest value below, 4 = get nearest value over, 5 = return “” if not found, 6 = interpolate

Select Case lookupType
Case 0 ‘ get first exact value
For i = 1 To Rl
If check = False Then
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
check = True
End If
End If
Next
If temp = “error” Then
temp = “value not found”
End If

Case 1 ‘ get last exact value
For i = 1 To Rl
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
End If
Next
If temp = “error” Then
temp = “value not found”
End If

Case 2 ‘ get nearest value
For i = 1 To Rl
If i = 1 Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
Next

Case 3 ‘ get nearest value below
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) <= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
End If
Next

Case 4 ‘get nearest value over
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) >= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
temp = resultsArray.Cells(i, 1)
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
temp = resultsArray.Cells(i, 1)
End If
End If
End If
Next

Case 5 ‘linear interpolation
check = False
temp1 = 0
temp2 = 0
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) <= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
Ra = Val(resultsArray.Cells(i, 1))
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
Ra = Val(resultsArray.Cells(i, 1))
End If
End If
End If
Next
a = temp1
check = False
temp1 = 0
temp2 = 0
For i = 1 To Rl
If Val(lookupArray.Cells(i, 1)) >= value Then
If check Then
temp1 = Val(lookupArray.Cells(i, 1))
Rb = Val(resultsArray.Cells(i, 1))
Else
temp2 = Val(lookupArray.Cells(i, 1))
If Abs(temp2 – value) < Abs(temp1 – value) Then
temp1 = temp2
Rb = Val(resultsArray.Cells(i, 1))
End If
End If
End If
Next
b = temp1
If a = b Then
temp = Ra
Else
temp = ((value – a) / (b – a)) * (Rb – Ra) + Ra
End If

Case 6 ‘return “” if not found
temp = “”
For i = 1 To Rl
If lookupArray.Cells(i, 1).value = value Then
temp = resultsArray.Cells(i, 1)
End If
Next

End Select

Ulookup = temp

End Function