Hey guys, this is 7th post for TIS Engineer Advent Calendar 2015.
First of all
This article is totally unrelated to our company, and only expresses my personal thought. Just one more thing, I wrote this last Sunday. You know, I'm seriously busy at work on weekdays and have no extra time to write wonderful post like this.
Excel VBA, the AWESOME
Are you satisfied with your daily exceling life? you might use tons of amazing functions, and build magical plotting papers effectively in your Excel environment to finish your professional works, as a Pro-Excelist.
I know this is excellent, but did you know VBA has a kind of incredible powers that make your enterprise skill greater approximately 1024 or 2048 times than now you have?
So, what is VBA? The philosopher's stone Wikipedia says like below.
Visual Basic for Applications (VBA) is an implementation of Microsoft's event-driven programming language, Visual Basic 6, and its associated integrated development environment (IDE).
Visual Basic for Applications enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). It supersedes and expands on the abilities of earlier application-specific macro programming languages such as Word's WordBasic. It can be used to control many aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes.
As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. For example, it can be used to automatically create a Word report from Excel data, which are automatically collected by Excel from polled observation sensors. VBA has the ability to use (but not create) (ActiveX/COM) DLLs, and later versions add support for class modules.
VBA is built into most Microsoft Office applications, including Office for Mac OS X (apart from version 2008) and other Microsoft applications such as Microsoft MapPoint and Microsoft Visio, as well as being at least partially implemented in other applications such as ArcGIS, AutoCAD, CATIA, Corel Draw and WordPerfect.
Cool! VB .Net? What are you talking about. 6 is fine, and this is standard enterprise version for all SIers, I guess.
How to learn Visual Basic grammar or something
Skipped, because this is boring.
Don't worry, there are no difficulties, so I bet you can learn it easily by yourself. Just do google.
Leveraging WinAPI
This is what I might have wanted to show you guys today.
WinAPI is the one what gives you formidable powers as so-called Arch-Excelist that you could not be able to obtain in normal use of Excel. What the heck does it mean? The truth is, you can control not only Excel objects, but also windows environment itself, with this DIVINE POWER.
You're right, WinAPI is core set of application programming interfaces in Windows, not related to Excel VBA directly. But that's seriously meaningless explanation in this article.
Now, it's time to break out of your shell and become Arch-Excelist.
Why don't your experience SLEEP?
Open your Excel, and try following steps.
1. Check KAI-HATSU in Ribbon-NO-User-SETTEI panel, Option menu
2. Push Visual Basic item
3. Select HYOJUN-Module menuitem in SO-NYU menu
4. Declare API what you want to call
This is pretty significant charm to enable WinAPI in your workbook.
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
By writing this declaration, Windows gets ready to help you.
5. Code your powerful imagination without any hesitations
Write down the source code what you want to write, in ThisWorkbook. Here is a neat example.
Public Sub example1()
Call Sleep(5000)
Call MsgBox("1024", , "exterprise")
End Sub
Guess what? There is the fun of just doing the next step and seeing what happens. Now, let's push play button.
6. Push JIKKO button
Just JIKKO it!
7. Result
Excellent!
Hey, did you guys see that? You could have experienced that a message box appeared in 5 second after you started your macro.
The Other APIs
The Sleep API has showed its power to you guys, but that is the weakest function, and a disgrace to us WinAPI.
Windows has so many functions what you can call, but I had to omit the description about them for want of space. Instead of that, check these out.
You couldn't find how to declare them in VB? Alright, just google again.
Now you guys have read this far, I bet you have started believing that WinAPI must allow you to do everything you want such as creating windows, locating items on them, taking desktop screen shots, logging and emulating all keyboard/mouse operations, sending them to another pc, shutting down your computers on your Excel workbook. Correct, that is the first step to become Arch-Excelist.
https://www.youtube.com/watch?v=qosPN-wOd9E
Conclusion
This article was super, wasn't this.
More importantly, if you want to get various kinds of knowledge as a powerful System Engineer correctly, you should read System Engineer Advent Calendar 2015.
The next post
koudaiii is going to take care of the next one.
I can't wait to see that.