When I first joined last year, I admitted to my director that I felt I was getting nowhere. He just smiled and told me how things worked in layman's terms in our department.
"Imagine a dark room. You spend about 3 months searching for the light switch, and whenever you do, you think you know everything. Whenever you feel like you do, in front of you lies another door that leads to another bigger, darker room.. and the cycle goes on."
The interesting thing about these "rooms" is that they're always full of interesting knowledge and skills to learn.
And he's right. Up to date, I still have questions why rainbow shoots out from our Job-Runners occasionally.
The fascinating thing is that the job consists of a minimal set of Access SQLs. For whatever reasons, I will not state why we need them.
But the other thing I'd like to talk about is my adventure in writing stuff like this at work..
----
Sub test()
MsgBox prompt:="Welcome, " & Application.Username & "!"
End Sub
----
And then, you embed that procedure into a button for a user to click in Excel, and a message pops up:
Now for those who knows me before I started working in Aztec (or right now, but you probably think I'm around and about rocking my brains out with my guitar), I'm not a programmer or any sort, and trust me, I'm still not!
Because of being exposed to what I do at work, and a little bit of Excel, I started off with building "prototype" templates that made things work a little bit easier for me.
Automation, turned into a form of art and beauty for me.
Where a user clicks on a dropdown menu, and whenever the value is selected, it determines particular cells in a workbook to change values to suit the reference.
Now that's all child's play, for some reason.
When I first saw, what I called then, the hacker's screen, which the world knows as Visual Basic for Applications, I didn't know what I gotten myself into. Something like the above turned into..
----
Sub test()
Dim Msg As String
Dim UserInput as String
Msg = "Welcome! Please provide your name."
UserInput = InputBox(prompt:= Msg, Title:= "I need your name!")
If UserInput = "" Then Exit Sub
MsgBox prompt:="Welcome, " & UserInput & "!"
End Sub
----
I'm like.
Holy shit. I just made one of those cool boxes that tells me that.. it requests for my name, and .. It just greeted me!
Like seriously.
Diarrhoea or holiness.
For the past 3 weeks, I've embarked on a journey that sort of made my "dream" come true. Sharing my knowledge and capabilities with others. I've done it with music - if you ever had a personal chat with me - and I never thought I was
Now, it wasn't as easy as I thought. Because if I were to explain how I used the Circle of Fifths and a Modern Dorian mode to write the last section of a song to make it sound majestic, or the Pitch Axis Theory to write a song that doesn't have a fixed key to the common passer-by, they would be, well, a passer-by.
But if you demonstrate it in a context where they have knowledge in, which is to play for them, then they'll pay attention.
I didn't realise that designing my first public-release Add-In at work required so much resources from myself. I was my own:
- VBA Programmer
- Designer
- UAT Team (User Access Testing)
- Troubleshooter
- Project Director
.. And then I had to study a little bit of XML to make stuff work particularly in Excel 2010.
The idea I had in mind when I was driving this minor project of mine, was an iPad.
Now now, before any anti-Apple people get on my tail about a product that's rubbish, you must understand one thing about it.
You don't need an instruction manual to operate an iPad. Let's have a look at the product design.
- The iPad has only 4 buttons and a I/O switch (for muting the device or screen orientation lock.
- It's got a screen.
This means that it won't take you long to figure out which is the button to bring the magical chunk of technology to life.
And whenever you do, you only use one button to navigate around the entire device. Which is to return you from your applications to your Home screen.
That button is placed in a manner where you know you'll always access it.
The interesting thing about this product (or similar greatly designed products) is that you need no instructions for it, and the learning curve is not steep either. I remember reading this in an article, but this seems to be a little bit more interesting if you want to talk about product design.
Now, the Add-In I made, was a test. There was one that I released to a set of people on another department within the company (and a few friends), it has no instructions, but it sort of reinvented how the wheel works.
For months of VBA programming, I've been retrieving a list of existing worksheets that exists within a workbook for my own use and report building.
And not long ago, I discovered how to make a UserForm usable in Excel. Designing how it works, however, was a pain, because it was brand new to me, and I suck in visual design. It's just as bad as my cooking, where I've set more oven mittens on fire than getting something edible out of the kitchen to be served.
When it hit me I had a little bit of control over UserForms, I could use a UserForm to report to the end-user to show the list of worksheets that exists within the workbook.
Holy shit. What the hell am I thinking of?
So. Imagine a pop-up window that appears in your face, and shows you the list of worksheets you have in your current workbook (hidden or unhidden), and you have a choice to select multiple worksheets you wish to hide or unhide.
Stop. And read again: you have a choice to select multiple worksheets to unhide. And this is Excel we are talking about.
Excel has a limitation - which is when it has multiple hidden worksheets, it only unhides one at a time, based on user selection.
I've seen how reports are built in the company. And sometimes, they have 50 hidden data sheets.
Think about the time you can passionately spend with Excel to unhide 50 hidden sheets when you need to find out what went wrong somewhere.
Not cool at all.
Moreover, if you're a data nazi, you'd like to have everything to be consistent. So selecting 50 worksheets that end with the same keyword alone, can be quite frustrating - especially if you accidentally selected a wrong worksheet.
The toughest part was to let it sit in a normal user's hands. Sure, I'd understand how the code works, but that doesn't matter at all when it comes to the user. Nobody cares about how much effort Apple puts into their electronics design, and majority of the market what matters is that the product is light, convenient, and easy to use.
Don't get upset with me when I mention the above. Try explaining how great Apple is by making their electronic components microscopic to build a thin table to a child, and take note of the time when the child loses his/her attention in your lecture and starts using the device itself.
I had to go with the same direction. I had to allow the user to not let the user spend too much time to adapt to it.
I didn't need to explain much when I sent it to them, and they find this Add-In very easy to use.
And because they can, now, unhide 500 worksheets at once (as opposed to Excel's limitations), they find it very useful.
That's just one of them. The other that the people in my department doesn't know is that, there's a button where when they find an ID and wishes to identify it without going through an application, all they need to do is to click that button and..
(This idea came from my director, and we both worked on it.. Was like a dream come true as I look up to him when it comes to these things.)
(This idea came from my director, and we both worked on it.. Was like a dream come true as I look up to him when it comes to these things.)
- The workbook creates a connection to one of the SQL servers in the backend.
- It retrieves the ID, and dumps it into a string.
- And then it sends the query to the SQL server to retrieve the information in a nice manner where they don't have to strain their eyes to see the attributes of the ID that they want to be identified.
This was my first ad-hoc product that I designed for the department, and I'm happy that I've dumbed things down in plenty of details so that users of most levels of users can use it.
It has been an interesting experience. The codes, the designing of buttons, troubleshooting it, testing it, criticising it, it all fell into place at the same time..
Now the only problem is, like the words of Jony Ive of Apple:
"The thing is, it's very easy to be different. But it's very difficult to be better."
That's when we strive on to be better than where we stand now.
