Today suddenly I need to write something by hand. I tried to find lined templates on the internet but couldn’t find any that is free (unbelievable!). I know it’s easy to create such a template using Word but sometimes you just don’t have the time.
So here’s what I whipped up, enjoy it for the great price of FREE!
At first I thought I have to define three languages, because Unikey automatically made Windows switch to Vietnamese when I type, the default keyboard switching shortcut also conflict with Unikey’s (Ctrl + Shift); so every time I want to switch between QWERTY and DVORAK, I have to check whether I had accidentally turned Unikey off, and I have to press the language switching shortcut two times to switch between the two most used language (English and Korean). Believe me, that’s a lot of hassle right there.
So, after a lot of fiddling, I figured out I could just have it like this
(To add Dvorak to Korean, select “show more…”)
This way, I just need to switch between korean mode and English mode by pressing Ctrl + Shift (Microsoft IME still uses the QWERTY layout, which I don’t want to use, so I left it in 영어 mode). Conveniently, when I switch to Korean, Unikey is also turned off so I won’t accidentally put accent on Korean characters.
So, to summary:
To Type English, I can just type in Dvorak
I can also type Vietnamese, since VNI typing does fit really nice with English
Press Ctrl + Shift to switch to Korean, which also turns Vietnamese off
I seem to have a special bond with ancient stuff. I were doing Pascal when C++ and WinForm was at their prime. Now I’m toying with a discontinued Microsoft Office feature. I just can’t help it, I have to work with Office 2003 around the clock since the upper IT management takes about a decade to certify a new software as “compatible”. Well, working for a big corporation has its pros and cons and we all have to cope with them.
I don’t feel the Office assistant intrusive though, it certainly doesn’t get in your way if you turn it off, but oh well, only when you know how. (Which, sadly, is not the case for many computer users). And I don’t think we can just let the woman take it from us. Gdzie jest czcionka? 😉
When I was in 7th grade or something. I read an article on PCWorld Vietnam about how to manipulate the Office Assistant. I diligently typed the code (in VBA) character-by-character without even understanding them :P. Nevertheless, the result was really satisfying, I were able to make the cat (Links, an office assistant character) jump through hoops (literally).
Okay, enough trivia! I don’t remember any function from back then so I’ll have to start over. I have done various applications inter-operating with Microsoft Office’s VBA before, so I guess it would be an easy task. I added the Microsoft Excel’s object library to my C# project and search for “assistant”. Lucky me, something popped up.
Assistant class in object browser
As you may see, it’s a child of the application class so you’ll have to start an Office application first. I chose Excel because it’s the application I have been working with the most. For aesthetics reasons, I tried to hide the Application’s window but that will hide the assistant too :(. Apart from that, thanks to the well-built COM interface, initialize and display the assistant is easy.
ApplicationClass Test = new ApplicationClass();
Test.Assistant.On = true;
Test.Assistant.Visible = true;
In case you are wondering, ApplicationClass is used to control Excel. All the possible action of “Assistant” is neatly listed within an enum so I just have to convert those to text and list them for the user to choose. No button-to-button editing for each action required!
foreach (string TypeName in Enum.GetNames(typeof(Microsoft.Office.Core.MsoAnimationType)))
Because you can only see the Office Assistant when the host Office application has focus, I made a timer so the assistant will do the same action over and over, so the user can switch to the relevant application.
You can also make the assistant say stuff you want:
private void button1_Click(object sender, EventArgs e)
// I created a button and a TextBox
// When you enter some text into the TextBox and click the button, the
// assistant will make a speech balloon with the text in it
timer1.Enabled = false;
MessageBox.Show("Switch to Excel after clicking OK");
Microsoft.Office.Core.Balloon Speech = Test.Assistant.NewBalloon;
Speech.Heading = "Test balloon";
Speech.Text = textBox1.Text;
Speech.Mode = Microsoft.Office.Core.MsoModeType.msoModeModal;
timer1.Enabled = true;
So if you are still stuck with Office 2003 and wanted to play around with it a bit, here’s the compiled application (requires .NET 2.0 and Microsoft Office 2003 installed) and for those who want the code, here it is.
My work is not mainly about Excel but people often come to me for solution to their everyday computing problems. Eventually after several months of repeating the same thing to many people, I decided to sum up all the questions and make a training session covering simple but many people just don’t know they existed in Excel.
I am not intending to teach people how to use Excel but how to use it efficiently. So my method is mostly visual-based: I use a lot of images to illustrate concepts so they can recall what I’ve said later easily. I intended to make some practice exercise to reinforce their knowledge too but my folks were strongly oppose against homework so that idea was abandoned :p
The first session have 4 attendants, which I considered a successful experiment. It lasted for around 45 minutes and covered the following topics:
Counting with multiple conditions
Visual Basic for Application basics
I intended to record the session for later broadcast but forgot it on that day. So if you are interested, here are the slides at screen resolution (72 dpi i.e. it look pretty crooked) together with notes in PDF format. Powerpoint file is available upon request if you want to conduct similar sessions. I love to spread knowledge 🙂
* For the real action, jump straight to the “with VBA” section
It’s there a decade ago, it started off the wrong foot, generated some (bad) publicity and people started to wonder why it’s there in the first place. It’s quite surprising how people are ignoring it by now. It’s just sitting there in the corner of the file system, doing various sort of slavery work for people. Visual Basic for applications (VBA), imagine how we missed ya!
When I was a kid, I once thought that by choosing the path of a developer, I would sooner or later encounter it and do various sort of wondrous stuff with it, like some kind of smart worksheet that won’t let you type character data into numeric cells.
Sadly, that never happened. There are other ways to do that without VBA, or even without Excel since now I know, there’s Open Office and all. VBA’s role is even somewhat superseded by Visual Studio tools for Office/Applications.
Despite all that, VBA is still there, in the latest (official) version of Office for you… There’s no reason for it to wait any longer, right? This is intended to be a short (and simple) tutorial covering a fraction of what VBA can do for an average user, so you don’t have to be a total nerd to follow this.
Excel comes with a huge load of functions (hundreds or so) serving various purposes, but sometimes even that is not enough! Let’s say you want a function that returns the last working day before a certain date. Doing this week Excel’s function could be tedious because you need to handle three separate cases which will require two IFs:
– If the given date is Sunday, the last working date is last Friday (Two days backwards).
– If the given date is Monday, the last working date is also the last Friday (Three days backwards).
– For every other day of the week, it’s just the previous day
And also, WEEKDAY() returns a number from 1 to 7 so you have to remember which number corresponds to which day to make the IFs. The finished function should look like this:
Give that function to someone who haven’t read the two paragraphs above, it will take them at least 5 minutes to figure out what are you trying to do in that cell, and then you’ll waste yet another comment to explain that to them!
You can program a new function to do the above.
1. From Excel press Alt+F11 or open the following menu/ribbon:
2. Select new module from the toolbar
3. Type in the following code in the new window
Public Function BusinessDayPrior(dt As Date) As Date
Select Case Weekday(dt, vbMonday)
BusinessDayPrior = dt - 3 'Monday becomes Friday
BusinessDayPrior = dt - 2 'Sunday becomes Friday
BusinessDayPrior = dt - 1 'All other days become previous day
It will look like this
4. Click save, return to the main excel window, type a date you want and in C14 and =BusinessDayPrior(C14) (replace C14 with any cell of your choice), the result will look like this
Much clearer, and for an exercise, try to handle the case to suit your own specific need: tweak that function so it will handle holidays too:
– What if the given date is right after a one-day long holiday?
– What if the given date is right after a several days long holiday?
– What if you have compensated holidays (e.g. if the holiday is on weekends, you get another day off)
Imagine this scenario: You are browsing through a list of stock quotes and you want to take note of profitable stocks on another sheet. If you are doing this manually you’ll have to copy the stock name, switch to the other sheet and paste it. With VBA you can have a button on the sheet that when you click, will do all three steps for you.
1. Open the visual basic editor with Alt + F11 and create a new module (see above on how to do it)
2. Add the following code to the window
' Macro1 Macro
' Macro recorded 14/10/2009 by SilentWind
Dim Temp As Double
Temp = ActiveCell.Value 'Get selected cell value
For i = 1 To 65535 'Scan the destination sheet…
Range("A" + Trim(Str(i))).Select
If (ActiveCell.Value = "") Then '…for the first empty cell
ActiveCell.FormulaR1C1 = Temp 'Copy it to the destination sheet
3. Draw a button on the sheet:
– In Excel 2007, go here
– In Excel 2003, right-click the toolbar, select customize, and drag the button onto the sheet
– In place of the button, you can even you an auto shape! Contrary to popular belief, it does not always require a button to do some useful task!
4. Right click the newly added button/shape and select Assign Macro…
5. Select Macro1 (the one you created at step 2)
Now, every time you select a stock name and click on the button/shape, it gets automatically added to the last empty cell in column A on sheet3.
In Excel 2003, you can drag the button onto the toolbar instead; the steps are still the same. In Excel 2007, you can only add that button to the Quick Access toolbar (well, unless you know how to use VSTO…):
– Right click the Quick Access toolbar and select Customize Quick Access Toolbar…
– Add your macro by selecting “Macros” from “Choose commands from”, select your macro and click add.
I think the two examples above are enough for casual user to understand and adapt for their daily use. Application is endless! Should there be any other questions, feel free to ask; I will add them to future posts if they are interesting enough.