VBA – User Defined Type

As much as I do not like VBA, I do like the “User Defined Types” which, from a C/C++ programmer’s prospective, this is a lot like using structs.  And for data management, which is probably 90% of what VBA is used for, it makes a lot of since.  How do we define a User Defined Type?

Private Type dict
    a As Integer
    b As String
    c As String
    d As Integer
End Type

Once Defined, we need an initialization function to store the struct.

Private Function defineDict() As dict
    defineDict.a = 1
    defineDict.b = "two"
    defineDict.c = "three"
    defineDict.d = 4
End Function

At this point, we can use the same calling to to that ‘struct’ to change the variables as needed.

Sub testType()
    Dim t As dict       ' Set the variable
    t = defineDict      ' Initialize
    Debug.Print t.b     ' Prints >> "two"
    t.b = "no more"     ' Change a variable
    Debug.Print t.b     ' Prints >> "no more"
End Sub

Minesweeper Guide

So the classic game of Minesweeper is a great waste of my time.  I’ve found though, the secret to being successful is understanding the 2’s.  I’ll demonstrate with a series of screenshots.  I’ll remind my readers that the Minesweeper I use is Linux based, and Windows’ Minesweeper is somewhat different.  For example, the version I’m using will always have the upper left corner free and there is always a solution through logic.  Unfortunately, in Windows, there are times when no logic will fix it.

Now, I’ve created a program to solve the puzzles, but it’s no where near as fun.  For the cheat program (for Windows XP version) check out my tutorial.  It’s a good read on using C in Windows to pull information about a program, or hooking, as it is better known.

The key for all the graphics is:

  • RED = MINE
  • YELLOW = MAYBE
  • GREEN = CLEAR

The first three graphics will show a common pattern with a 5 and some a row including 2’s.  When 2’s are in a row, you can start eliminating.

EDIT: I plan to continue updating this, but for now I’ll post as is.

GZip with ZLib

In an effort to use zlib in VBA, I had to first learn to use it.  I’m looking to gunzip the XFDL files to use the data as an MS  Office plugin.  In tinkering with zlib’s GZip functions, I found them to be very much like normal file handling in C.  Here’s a link to the official zlib reference manual, GZip is the tenth section.  So, to test it, I made a “micro-gunzip” which does work well.  When compiling, don’t forget to link to libz.a using gcc -o out -i in.c -lz.   Other than that, enjoy!  The basics are covered in the comments.

#include <stdio.h>
#include <zlib.h>
/* gcc filename.c -o outname -lz */

int main(int argc, char *argv[]) {
	// variables
	gzFile gFile;           // gzip file object
	FILE *pFile;            // out file object
	char *filen = argv[1];  // get the name of the file
	char buffer[100];       // buffer for reading

	// open files
	gFile = gzopen(filen, "rb");        // same as file
	pFile = fopen("example.out","wb");
	if(pFile == NULL || gFile == NULL)
		perror("Error opening file");
	else {
	    // write
		while(!gzeof(gFile)) {
			gzgets(gFile,buffer,100);   // file object first
			fputs(buffer,pFile);        // write to plain tex
		}
		// close
		fclose(pFile);
		gzclose(gFile);
	}
	return 0;
}

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.

Python:

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"

VBA:

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!!

Fern Screensaver : Part 4

I’ve now completed the Fern Screensaver in Objective-C for the Mac!  Surprisingly, using Objective-C was not that far from C/C++.  In fact, much of my code could be pasted directly in.  That includes the variables, math functions, etc.  Also, Mac has done a good job of documenting it’s libraries so using the NSObjects was not that difficult between Google and a quick reference search along with the example screensaver code.  I’m going to hit a few key differences and then post the entire file I edited.  XCode does the templating pretty well.

First, all the variables are at the top of the code so they don’t change as the program advances frames.

// VARIABLES
float mat[4][7] = {
//...

@implementation ZackScreenSaverView

Secondly, I had to use implement my NSObjects at the start of the -(void)animateOneFrame method.  The size = [self bounds].size; calls the screensize.  It’ll take some time to get used to the method calls in Objective-C!

- (void)animateOneFrame
{
	NSRect dot;
	NSSize size;
	NSColor *color;
	NSBezierPath *path;
	float red,green,blue,alpha;

	size = [self bounds].size;

The framework has some good methods for getting random floats, or any integer type!

	// random number
	p = SSRandomFloatBetween(0.0,1.0);

Finally, it took some new methods to draw my dots to the screen.

	// set dot
	dot = NSMakeRect(jx,jy,1.0,1.0);

	// make dot
	path = [NSBezierPath bezierPathWithRect:dot];

	// draw dot
	[path stroke];

Other than that, the rest is pretty much the same.  I encourage those more used to Objective-C and programming for Mac products to comment about the use of this code and correct any bad assumptions!  Here is the code in it’s entirety (note that I’ve taken out the redraw at this time)…

//
//  ZackScreenSaverView.m
//  ZackScreenSaver
//
//  Created by Zachary D. Skelton on 5/30/10.
//  Copyright (c) 2010, Skelton Networks. All rights reserved.
//

#import "ZackScreenSaverView.h"

// VARIABLES
float mat[4][7] = {
	{0.0,0.0,0.0,0.16,0.0,0.0,0.01},
	{0.85,0.04,-0.04,0.85,0.0,1.6,0.85},
	{0.2,-0.25,0.23,0.22,0.0,1.6,0.07},
	{-0.15,0.28,0.26,0.24,0.0,0.44,0.07}
};
// constant sets
float xa = -5.5;
float xb = 6.5;
float ya = -0.5;
float yb = 10.5;
// x and y to be altered
float x = 0.0;
float y = 0.0;
// screen/image size
int ix = 512;
int iy = 512;
// floats for math fulction
float p = 0.0;
float x0 = 0;
float jx = 0;
float jy = 0;
// variables to control function
int i = 0;
int k = 1;
int end = 10000;

@implementation ZackScreenSaverView

- (id)initWithFrame:(NSRect)frame isPreview:(BOOL)isPreview
{
    self = [super initWithFrame:frame isPreview:isPreview];
    if (self) {
        [self setAnimationTimeInterval:1/30.0];
    }
    return self;
}

- (void)startAnimation
{
    [super startAnimation];
}

- (void)stopAnimation
{
    [super stopAnimation];
}

- (void)drawRect:(NSRect)rect
{
    [super drawRect:rect];
}

- (void)animateOneFrame
{
	NSRect dot;
	NSSize size;
	NSColor *color;
	NSBezierPath *path;
	float red,green,blue,alpha;

	size = [self bounds].size;

	ix = size.width;
	iy = size.height;
	dot = NSMakeRect(ix/2,iy/2,1.0,1.0);

	red = 255.0;
	green = 255.0;
	blue = 255.0;
	alpha = 255.0;
	color = [NSColor colorWithCalibratedRed: red
									  green: green
									   blue: blue
									  alpha: alpha];

	[color set];

	// random number
	p = SSRandomFloatBetween(0.0,1.0);

	// find random i
	if(p <= mat[0][6])
		i = 0;
	else if(p <= (mat[0][6] + mat[1][6]))
		i = 1;
	else if(p <= (mat[0][6] + mat[1][6] + mat[2][6]))
		i = 2;
	else
		i = 3;

	// super math function
	x0 = ((x*mat[i][0])+(y*mat[i][1])+mat[i][4]);
	y = (x*mat[i][2]+y*mat[i][3]+mat[i][5]);
	x = x0;
	jx = ((x-xa)/(xb-xa)*(ix-1));
	jy = (iy-1)-(y-ya)/(yb-ya)*iy-1;

	// set dot
	dot = NSMakeRect(jx,jy,1.0,1.0);

	// make dot
	path = [NSBezierPath bezierPathWithRect:dot];

	// draw dot
	[path stroke];

	// CLEANUP
	/*
	if(k == end) {
		k = 0;
		//sleep(5);
	} else
		k += 1;
	*/
	return;
}

- (BOOL)hasConfigureSheet
{
    return NO;
}

- (NSWindow*)configureSheet
{
    return nil;
}

@end

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!

Fern Screensaver : Part 3

Today, I had no Mac with me to work on my screensaver in Objective-C.  As you can see in my previous post, I used my python program and wrote it in C for SDL that works on either Windows or Linux.  Now, the basic principle is the same, I just need to output that location to the correct pixel plot method to use other systems.  I thought, since I was on Windows all day, I might as well use that to make the screensaver using Windows API.

I’ve had some use of Window’s API before to make a Breakout game for my children.  It is rustic at times but works.  I decided to not mess with OpenGL today and just use Win32 API and GDI methods.  Below you will find my complete source code and a link to download this screensaver.  Once downloaded, save it in C:\WINDOWS\System32\ and it will appear on your personalization menu.

A quick note before the code, for those unfamiliar with Windows API… there’s a lot of goobly gook here that looks scary.  It’s really not that bad but here’s the meat of the project:

  • Lines 8-34 are the same variables we’ve seen before.
  • Lines 36-40 are a ‘dot()’ method I made to clean the code (slightly).
  • After Line 42:
    • WM_CREATE is called on the start of the program.
    • WM_DESTROY is called on the completion of the program.
    • WM_PAINT is used to paint to the screen.
      • Here you see the same math as before until lines 78-82.
      • At Lines 78-82, I’m using Win32 API to create a color brush and paint it with the dot method.
    • WM_TIMER is called every millisecond in this program, it keeps the loop going.
      • InvalidateRect() will force a WM_PAINT message.
  • Everything that follows is not really applicable as it just meets the demands of the Screensaver library.

If you would like to try out this screen saver, download from this link and save the file to your C:/WINDOWS/system32/ folder.  Let me know how it works for you!

#define WINDOWS_LEAN_AND_MEAN
#include <windows.h>
#include <scrnsave.h>

#define TIMER 1

// VARIABLES
float mat[4][7] = {
    {0.0,0.0,0.0,0.16,0.0,0.0,0.01},
    {0.85,0.04,-0.04,0.85,0.0,1.6,0.85},
    {0.2,-0.25,0.23,0.22,0.0,1.6,0.07},
    {-0.15,0.28,0.26,0.24,0.0,0.44,0.07}
};
// constant sets
float xa = -5.5;
float xb = 6.5;
float ya = -0.5;
float yb = 10.5;
// x and y to be altered
float x = 0.0;
float y = 0.0;
// screen/image size
int imgx = 128;
int imgy = 128;
// floats for math fulction
float p = 0.0;
float x0 = 0;
float jx = 0;
float jy = 0;
// variables to control function
int i = 0;
int k = 1;
int end = imgx*imgy;
int done = 0;

void dot(HDC hdc, HBRUSH whtBr, int x, int y) {
        SelectObject(hdc, whtBr);
            Rectangle(hdc, x-2, y-2, x+1, y+1);
        DeleteObject(whtBr);
}

LRESULT WINAPI ScreenSaverProc(HWND hwnd, UINT message, WPARAM wParam, LPARAM lParam) {
    static HDC hdc;
    //static HGLRC hrc;
    PAINTSTRUCT ps;
    HBRUSH whtBr;
    static RECT rect;
    switch(message) {
        case WM_CREATE:
            GetClientRect(hwnd,&rect);
            imgx = rect.right;
            imgy = rect.bottom;
            int ret = SetTimer(hwnd, TIMER, 1, NULL);
            if(ret == 0)
                MessageBox(hwnd, "Could not set Timer", "ERROR", MB_OK);
            break;
        case WM_DESTROY:
            KillTimer(hwnd,TIMER);
            PostQuitMessage(0);
            break;
        case WM_PAINT:
            k++;
            p = (float) rand()/RAND_MAX;
            if(p <= mat[0][6])
                i = 0;
            else if(p <= (mat[0][6]+mat[1][6]))
                i = 1;
            else if(p <= (mat[0][6]+mat[1][6]+mat[2][6]))
                i = 2;
            else
                i = 3;
            x0 = ((x*mat[i][0])+(y*mat[i][1])+mat[i][4]);
            y = (x*mat[i][2]+y*mat[i][3]+mat[i][5]);
            x = x0;
            jx = ((x-xa)/(xb-xa)*(imgx-1));
            jy = (imgy-1)-(y-ya)/(yb-ya)*imgy-1;
            // Paint it
            whtBr = CreateSolidBrush(RGB(255,255,255));
            hdc = BeginPaint(hwnd, &ps);
                dot(hdc, whtBr, (int)jx, (int)jy);
            EndPaint(hwnd, &ps);
            ReleaseDC(hwnd, hdc);
            if(k == end) {
                Sleep(5);
                done = 1;
            }
            break;
        case WM_TIMER:
            if(done == 0)
                InvalidateRect(hwnd, NULL, FALSE);
            else {
                InvalidateRect(hwnd, NULL, TRUE);
                k = 0;
                done = 0;
            }
            break;
        default:
            return DefScreenSaverProc(hwnd, message, wParam, lParam);
        }
    return 0;
}

BOOL WINAPI ScreenSaverConfigureDialog(HWND hDlg, UINT message, WPARAM wParam, LPARAM lParam) {
    //return FALSE;}
    switch(message) {
        case WM_INITDIALOG:
            //get configuration from the registry
            return TRUE;
        case WM_COMMAND:
            switch(LOWORD(wParam)) {
                case IDOK:
                    //write configuration
                    EndDialog(hDlg, LOWORD(wParam) == IDOK);
                    return TRUE;
                case IDCANCEL:
                    EndDialog(hDlg, LOWORD(wParam) == IDOK);
                    return TRUE;
            }
    }
    return FALSE;
}

BOOL WINAPI RegisterDialogClasses(HANDLE hInst) {
    return TRUE;
}

Just a note, this is the rare time you will find me messing with Windows.

Fern Screensaver : Part 2

For those interested, here is roughly the same code in C for use with SDL. This first block are standard variables that are not by default when CodeBlocks produces an SDL template.

#include <time.h>
#include <unistd.h>

    // START RANDOM NUMBERS
    srand((unsigned)(time(0)));

    // VARIABLES
    float mat[4][7] = {
        {0.0,0.0,0.0,0.16,0.0,0.0,0.01},
        {0.85,0.04,-0.04,0.85,0.0,1.6,0.85},
        {0.2,-0.25,0.23,0.22,0.0,1.6,0.07},
        {-0.15,0.28,0.26,0.24,0.0,0.44,0.07}
    };
    // constant sets
    float xa = -5.5;
    float xb = 6.5;
    float ya = -0.5;
    float yb = 10.5;
    // x and y to be altered
    float x = 0.0;
    float y = 0.0;
    // screen/image size
    int imgx = 512;
    int imgy = 512;
    // floats for math fulction
    float p = 0.0;
    float x0 = 0;
    float jx = 0;
    float jy = 0;
    // variables to control function
    int i = 0;
    int k = 1;
    int end = imgx*imgy;

    // dot rect
    SDL_Rect dot;
    dot.x = screen->w/2;
    dot.y = screen->h/2;
    dot.h = 1;
    dot.w = 1;

This next block is the heart of the program. You’ll see this is very close to the Python code, but be aware that in C, you’re needing to be careful about your defined types.

        // DRAWING STARTS HERE

        // random number
        p = (float) rand()/RAND_MAX;
        // find random i
        if(p <= mat[0][6])
            i = 0;
        else if(p <= (mat[0][6] + mat[1][6]))
            i = 1;
        else if(p <= (mat[0][6] + mat[1][6] + mat[2][6]))
            i = 2;
        else
            i = 3;
        // super math function
        x0 = ((x*mat[i][0])+(y*mat[i][1])+mat[i][4]);
        y = (x*mat[i][2]+y*mat[i][3]+mat[i][5]);
        x = x0;
        jx = ((x-xa)/(xb-xa)*(imgx-1));
        jy = (imgy-1)-(y-ya)/(yb-ya)*imgy-1;
        // set dot
        dot.x = (int) jx;
        dot.y = (int) jy;
        // draw bitmap
        SDL_FillRect(screen, &dot, SDL_MapRGB(screen->format, 255, 255, 255));

        // DRAWING ENDS HERE

Finally, we control the program flow. I found that it looks like nothing is happend once the drawing is a quarter complete. So instead of running through every pixel, we’ll just do a quarter of them. This speeds up the program a bit. I’m thinking I may let them go but start a new one when each is a quarter done.

        // CLEANUP
        if(k == end) {
            k = 0;
            printf("Out of dots.\n");
            sleep(5);
            SDL_FillRect(screen, 0, SDL_MapRGB(screen->format, 0, 0, 0));
        } else {
            k += 1;
            printf("\r%i out of %i",k,end);
            fflush(stdout);
        }

        SDL_Flip(screen);

Edit: For Windows users, you’ll need to have #include <Windows.h> and change sleep() to Sleep().

Fern Screensaver : Part 1

So my brother has asked me to help in making some iPhone/iPad applications.  Sounds like a great idea, I just need to get used to Objective-C.  If you already know C/C++, it’s not a huge leap to produce some iPhone code.  But I’m short of ideas to write and since most of the Mac is using Objective-C, I’ve decided practicing that language with something else, a screen saver!

I have done a simple screen saver using this excellent article to draw shapes.  I’ve decided to go from utilizing already produced code to doing something of my own.  My wife, who is a botanist at Iowa State was kind enough to loan me a Mac with Snow Leopard loaded, as school is out for the summer and they do not need this for their classes.  I asked her what she would like, and of course, it’s ferns (that’s the plant she studies).  Some quick thinking and googling lead me to the idea to use fractals!  In particular, Barnsley’s Fern.  To learn more, use google and this link.

Math drew this with a fractal equation, not by hand.

Now, as I have shown previously, I’m much better with Python, so to prototype the mathematical equation and code idea, I’ve developed a sample using pygame.  I found the math through this excellent code snippet, which saved me a lot of time in testing.  The use of the code is explained in the comments:

#!/usr/bin/python
import random, pygame, time
from PIL import Image
from pygame.locals import *
from pygame.color import THECOLORS as color

# VARIABLES TO INCLUDE MATH
mat = [[0.0,0.0,0.0,0.16,0.0,0.0,0.01],
	[0.85,0.04,-0.04,0.85,0.0,1.6,0.85],
	[0.2,-0.25,0.23,0.22,0.0,1.6,0.07],
	[-0.15,0.28,0.26,0.24,0.0,0.44,0.07]]

xa = -5.5
xb = 6.5
ya = -0.5
yb = 10.5

x = 0.0
y = 0.0

imgx = 700
imgy = 700

# VARIABLES FOR CONTROL
k = 1
end = imgx*imgy / 10 * 4 # Looks like nothing is happening after this point.

# START PYGAME AND SETUP
pygame.init()
screen = pygame.display.set_mode((imgx, imgy), 0, 8 )
pygame.display.set_caption('Fern Screensaver')
screen.fill(color['black'])

# START LOOP
done = False
while not done:
    # DETECT END OF DRAWING
    if k == end:
        # ENJOY AND RESET
        time.sleep(3)
        k = 0
        screen.fill(color['black'])
    else:
        # MATH FOR THE FRACTAL
        p = random.random()
        if p <= mat[0][6]:
            i = 0
        elif p <= mat[0][6] + mat[1][6]:
            i = 1
        elif p <= mat[0][6] + mat[1][6] + mat[2][6]:
            i = 2
        else:
            i = 3
        x0 = x * mat[i][0] + y * mat[i][1] + mat[i][4]
        y = x * mat[i][2] + y * mat[i][3] + mat[i][5]
        x = x0
        jx = int((x-xa)/(xb-xa)*(imgx-1))
        jy = int(imgy-1)-int((y-ya)/(yb-ya)*imgy-1)
        # AFTER PLAYING WITH SEVERAL COLORS, WHITE ON BLACK IS THE BEST
        c = color['white']
        screen.set_at((jx, jy), c)
        pygame.display.update()
    # DETECT EVENTS TO END PYGAME
    events = pygame.event.get()
    for e in events:
        if e.type == KEYDOWN and e.key == K_ESCAPE or e.type == QUIT:
            done = True
    # CYCLE AGAIN
    k += 1

# DONE
pygame.quit()

So, the next step is to code this for the Mac… keep posted for that code/tutorial.