Tag Archives: visual basic for applications

NCOER Spreadsheet – Done

Okay, so I’ve updated my code and I’m ready to release to the world my NCOER speardsheet.  Here’s a quick tutorial on how to use it.

  1. Download the Excel 2007 or Excel 2003 version. (Right Click and Save As)
  2. Ensure that you have enabled macros [ Office 2007 ] or [ Office 2003 ] for your MS Excel.
  3. Open the file you downloaded.
  4. Enter the unit you need the report for in the textbox on the left.
  5. Click Update
  6. It will prompt you for your AKO Username/Password to access the data.
  7. Wait a short time and it will automatically load and filter.
  8. Print or export the data as needed.

And that’s it.  I know military types read this blog (proven through the Open XFDL project) so please comment and let me know your opinion!

NCOER Spreadsheet

Last week, I complained about how bulky VBA (Visual Basic for Applications) can be.  My current project is to create a spreadsheet to keep track of upcoming NCOERs.  This is an easy task in anything but the most used and abused application the admin personnel in the Army use, Microsoft Excel.  Now, the data is easy enough to pull once you get an SSL connection with this website (to access you need to use an AKO Username/Password.)  The Interactive Web Response System will allow you to pull data on any past, due or current NCOERS (Evaluations).  This is great as I can track our evaluations as they move up the chain and will see when they are late.  Unfortunately, the web designers have only sorted the information by last name.

So, my basic program is complete.  I can make a connection (fortunately, MSXML6.0 makes an easy post request to an SSL website… something that if using Python, is not an easy task.)  It would appear that it is utilizing Internet Explorer’s library for the connection.  Once the connection is made and the data is loaded, then I parse.  Here is where VBA gets bulky.  Fortunately, I can use a reference to VBScript’s RegEx object, but it is far from being as complete as say, any other regex engine in any other language.  On top of that, parsing text through splits and such is an amazing pain.  Here’s an example.


import re
test = 'A simple "test of the languages" will show how bad VBA is!'
a = re.findall(re.compile(r'".*"',re.I),test)
print "Result = %s" % a[0]
## Result = "test of the languages"


Private Sub CommandButton1_Click()
    ' must include Microsoft VBScript Regular Expressions 5.5 in references
    Dim re As New RegExp
    Dim testing As Variant
    Dim test As String
    ' to escape quotes "" can be used or chr(34).
    ' unbelievably, chr(34) is easier to read than ""
    test = "A simple " & Chr(34) & "test of the languages" & Chr(34) & " will show how bad VBA is!"
    re.Pattern = Chr(34) & ".*" & Chr(34)
    re.IgnoreCase = True
    Set testing = re.Execute(test)
    Range("A1").Value = "Result = " & testing(0)
    ' Result = "test of the languages" in "A1"
End Sub

See the difference?  I know it’s pretty close but keep in mind, this is a single line of text.  Multiple lines, large text files, etc. can be horrendous.  What in the world is left, mid and right anyhow?  I know 90% of this is that I refuse to touch this language if I can avoid it but still, this is ridiculous!!

Microsoft Access : XFDL Viewer – Introduction

I know, I said it yesterday that it is rare that I develop on Windows, but this is a long promised application.  In 2007, my unit administrator (in the U.S. Army Reserves) suggested code that would allow batch loading XFDL forms from MS Access.  Due to the scope of the Apps 4 The Army project, I was limited to using a web application.  Now that the project is submitted and done, I am free to do my original plan, which is code to do the same process in MS Access.

Starting this today, I realized how much I hate doing Visual Basic.  Particularly, VBA is very painful!!  It’s not that the language is bad but it just always feels bulky and pieced together to me.  It seems to lack the professionalism of C/C++ and the flow of Python.  But, I may be alone in that.

I have a question for my readers though. Would anyone have an interest in seeing this project on SourceForge?  The Apps 4 the Army project is no longer my intellectual right, but a desktop application, a MS Office Plugin, etc… that’s all good to make public.  So let me know, if I get readers saying we’d like to help; then I will happily move this project to SourceForge!