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.
Word 2007… Interesting piece of software, even enough to provoke a full-scale debate on ISO’s decision to have two standards at a time (isn’t standards are intended to unite people in the first place? :))) but that’s not in the scope of what I want to write right now. And no, I’m not going to tell you where to click in Word to start writing. You can read the good-but-is-the-only-blogging-article at Microsoft Office online for that.
Word is not perfect for blogging. For example the category management is just ill-designed. You’ll have to click a button, select the category and then insert it. The on-page category has a drop down box but the only item inside is “none” (what the…?) and yes, I am using the latest service pack. Possibly someone will eventually tell this to Microsoft to be fixed in future release, but hey, Word is not the only thing Microsoft released for the blogger community! There is Windows Live Writer (which is part of Microsoft new “live.com”-ing-everything strategy) too, which appears better suited for the purpose (the demonstration has pictures, categories and stuff listed in a nice view). Unfortunately it’s a 125MB download, and that’s not financially feasible for me to download (and blog about it here). You can download, try, and tell me instead 😛 [edit: a review about WLW can be found here, it cover most of what WLW have to offer, though it has given WLW quite a bias ;)]
UPDATE: WLW is actually only 5MB 🙂
Back to Word… good for text for not so good for blogs, you may as well encounter some problem blogging:
No HTML editing
Limited picture upload & management
I tried and solved some stuff ^^
Word is a Microsoft software, Live spaces is a Microsoft blogging platform. The irony? Live spaces is the only service that doesn’t provide Atom or XML-RPC so Word has to e-mail blog to Spaces, and because blogging is that hard, even if you read and followed the direction on Live Spaces help, Word will just pop an error up when you tried to blog with pictures. Also, wordpress.com won’t allow you to upload pictures either (self-hosted WordPress installations like mine do); and possibly some other service on Word’s “supported” list won’t either… The solution is uploading the pictures to a separate host. If you ever tried to click on”Picture Options” you’d see some choices
My blog provider: Doesn’t work because you are reading this 😛
Don’t upload picture: Awn, not helpful at all
My own server: Make 2 fields appear: upload URL and source URL
What is upload URL and source URL? Word help won’t tell you, Microsoft Office online won’t tell you either! It turned out after a couple of queries that, upload URL is something around an FTP address and source URL is the http address at which the picture should appear after being uploaded. The question turned to “what to fill in?” The links provided in the dialog box is just as helpless as the help 😛 You’ll have to find your own provider, which apparently must support the following
HTTP direct link
Some image hosts support this, photobucket does have a plan with FTP access but that’s not quite cheap :P. IMO, it’s best to use a web host as you could use it for more advanced purposes later ^^. In this example, I’ll demonstrate with freehyperspace5.com, a free hosting service which anyone can register. You could choose your own host but try to avoid:
Byet hosts: they have a high likelihood of deleting file storage only accounts even if their TOS doesn’t say so.
Any other host that specified in their TOS that they are against picture hosting
Register an account at your host, remember [your username], [your password], [ftp host] and [homepage address], then go to word and type
ftp://[your username]:[your password]@[ftp host] in the upload URL in Word, replace the square brackets with your own information. This looks something like ftp://wind:[email protected]
http://[homepage address] for source URL
Done! Press OK twice and now you are ready for picture (and smart art ^^) blogging with Word! No more manually uploading pictures and copy-paste the lengthy URL; just insert and publish! (For a demonstration, have a look at my previous posts, most of them are done with Word!)
One proof that Word 2007 is designed in such a hurry: Every time you want to blog, you’ll have to click the big red button (the office button :P), choose new, wait for the dialog to appear, choose blog post, OK, wait for the blogging interface to appear… Sick, isn’t it? To start Word exclusively for blogging, you can follow these steps
Browse to the word executable, usually it’s in C:Program FilesMicrosoft OfficeOffice 12winword.exe
Right-click and choose Create shortcut
Right-click the newly created shortcut and choose Properties, switch to the Shortcut tab
In the Target field, add /t “C:Program FilesMicrosoft OfficeTemplates1033Blog.dotx” /q, replace “C:Program FilesMicrosoft Office” with your path if necessary
The /t tells Word which template to use at start, the /q (supposedly) suppress the splash screen
Move the shortcut to where you want it to be: desktop, quick launch, or even start up if you want to blog every time you turn your machine on 😛
PS: After some more looking, it turns out that Blogger has its own plugin for Word long ago (download it here), it worked on Word 2000 and above but the interface is not that interesting and it still does not allow you to post pictures – according to this podcast. To me the plug-in appears unupdated for a while…