Excel VBA programming in a hurry

So I promised my finance director that we could perform a particular task with an Excel macro (because I know we can)... problem is, he heard "Big chief on high, I can perform this task for you".

Needless to say, I need to learn Excel VBA programming. Ideally, by tomorrow (clearly not going to happen).

I know BASIC and I'm very fluent with Excel. Combining the two is something of a grey area for me :)

I've had a quick dabble, enough that I know that I need to know the Excel object model (which I don't) and the correct way to implement user forms (or whether it's best just to attach buttons to a worksheet). I also need to know where my macro code should be going (attached to a worksheet, attached to the workbook or in a module).

I have to develop a system that will take a text file as input (standard format) and produce an invoice from the data in the text file. The invoice may span several pages. I have a sanctioned invoice template already, and I know (in theory) how to get from raw data to completed invoice. I just need to automate the process based on the minimal user input.

It will not be me doing the inputting, so I need to idiot-proof the whole thing as well :(


I already have on my list of essential purchases:

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel and VBA by Stephen Bullen, Rob Bovey, and John Green.
Excel VBA Programming for Dummies by John Walkenbach.

Is there anything else I should add to my shopping cart? Are there any particularly useful online guides or resources that I should know about?

One very useful technique. Have Excel record a macro for you, save it, and then open up the macro in the macro editor. I used that to learn a lot about macros in excel - that was years ago though.

I have been doing that - which has got me to a level whereby I know how to write code to select between different worksheets and change data in cells. I can also attach code to buttons and display user forms.

I just don't know the right way of doing things. I think the most desirable solution would be to create the VBA code and distribute it as an add-in for Excel. That way, it's in the menus and the office numpties can't delete it or overwrite it (I'm dealing with people who have no real clue what a folder is, or how to send a link in an email instead of the actual file which they want updating by everyone in the office).

All I want is a simple app that presents the user with three very easy to understand options (month and year of the invoice to be created and a button that says "create invoice" which prompts for the text file to take the data from). They then print the invoice, or email it, or they come and ask me for the umpteenth time how to export it as a PDF.

**EDIT** I've no doubt I could muddle up a solution, but it would be woefully inelegant and prone to user error. I would end up getting the blame for the user error :^}

I would do this with a VB application, rather then a macro. Depending on the version of excel you are using VB6 or .Net would work. You can still use the macro editor to figure most of it out.

The reason to use a seperate program is that way the resulting excel file can be macro free (emailing with a macro can be problematic) - and the Bumpkins have no way of messing up the actual program.

If you have to go the macro route - try putting the original file on a secured shared drive, give the users the rights to read from it only. This will keep them from being able to delete/modify/destroy the original.

Hmm... food for thought there!

I don't have the option (at work) of developing a VB app. Our IT guys will soon discover I've got 'unauthorised' software installed and they will come and uninstall it (they won't even let me install Opera or Firefox).

I could develop it from home, but they really don't pay me enough :) They also wouldn't allow me to take any data home with me (security concerns). They similarly will not let me give out my eFax number to customers, insisting instead that I make our customers fight to get through to the single shared fax machine in our office.

I hasten to add, I don't work for a company that produces or sells security sensitive material. We sell pens and paper to businesses.

Looks like you are going to need to go with the secured macro version. I have not had to do this before (always using a VB program), but I think putting the code behind buttons may be the best bet in this case. (one button to load the file and review perhaps, another one to load an existing file and email it, etc...)

Thanks dynaman. Tomorrow is the first day that I actually get my hands on usable data. It's also the day the customer needs the invoice (plus an accompanying report).

I've also got my actual job to do. It's going to be a fun day :D

Vinylpusher,

Excel VBA is quite good to use. There are a number of ways to access worksheets and transfer data around.

1. Use a worksheet to temporarely store you import data (you can process each line manually (+ validate) or you should be able to use the built-in functions to open the file as a worksheet and go from there).

2. Accessing data on worksheets - there are a couple of different ways to access stuff, the best is generally to reference it, but if you need to cut/paste or format you will need to access directly.

Examples:

Reference:

	Dim worksheetA as Worksheet
	Dim worksheetB as Worksheet

	'Turn off screen updating
	Application.ScreenUpdating = False
	
	Set worksheetA = ThisWorkbook.Worksheets("SheetName")
	Set worksheetB = ThisWorkbook.Worksheets("SheetName")
	
	worksheetA.Unprotect Password:="password"
	worksheetB.Unprotect Password:="password"
	
	worksheetB.Range("A1").Value = worksheetA.Range("A1").Value
        With worksheetB.Range("A1").Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        
        End With

	worksheetA.Protect Password:="password"
	worksheetB.Protect Password:="password"

	'Turn on screen updating
	Application.ScreenUpdating = True
	
	Set worksheetA = Nothing
	Set worksheetB = Nothing

The benefit of this method is that it is much faster and you don't need to change between worksheets, but formatting can be harder to do (best for pre-formatted templates)

Direct Access:

	'Turn off screen updating
	Application.ScreenUpdating = False
	
	Worksheets("SheetNameB").Activate
	Activesheet.Unprotect Password:="password"
	Range("A1").Select
	Selection.Copy
	
	Worksheets("SheetNameA").Activate	
	Activesheet.Unprotect Password:="password"
	Range("A1").Select
	ActiveSheet.Paste
        With Selection.Interior
            .ColorIndex = 6
            .Pattern = xlSolid
        
        End With
	Activesheet.Protect Password:="password"

	Worksheets("SheetNameB").Activate
	Activesheet.Protect Password:="password"

	'Turn on screen updating
	Application.ScreenUpdating = True

Is slower as you need to swap between sheets to do stuff (generally how code would be if you record macros). Generally easier to format on the fly.

Tips:

UserForms - pretty limited compared to standard Visual Basic 6 forms, so you can't do full-on stuff easily (say a wizard), but you can reference controls (see below)

Referencing controls - you can do this (particulary with Excel 2000 or above), but unless you can guarantee the control exists on the users machine, the workbook will fall-over when opening.

Recording macros - can be quite handy, but hand-coded stuff will perform better and probably be more accurate.

Option Explicit - good coding will always have this set - always much easier to debug.

Screen Updating - always turn it off and on as necessary - makes the program look more professional

Sub Auto_Open - use this sub when you startup as you can set the title of your application, maximise the screen and create menus or toolbars - again makes the product more professional.

Sub Auto_Open()
    'Set title
    Application.Caption = "Product Name"

    'Maximise Excel for user
    Application.windowState = xlMaximized

End Sub

Using ThisWorkbook in Project Tree -> Microsoft Excel Objects -> ThisWorkbook. You can do some good stuff here as well.

Private Sub Workbook_Activate()
    'Set title
    Application.Caption = "ProgramName"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim msgboxResponse
    
    'Prepare
    Cancel = False
    
    'Prompt closure
    msgboxResponse = MsgBox("Are you sure you want to exit ProgramName?", vbYesNo + vbDefaultButton2 + vbQuestion, "Exit Program")
    If msgboxResponse = vbNo Then
	'Stop closure of program
	Cancel = True

    End If
            
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Ensure user cannot save workbook
    'Cancel = True
    
End Sub

Private Sub Workbook_Deactivate()
    'User has changed from this workbook - reset excel caption
    Application.Caption = ""

End Sub

Oooh, you're clever :)

you are now my friend.