This Meeting was held at Judy Jeffery’s house.

We discussed the GST and some of the awkward accounting methods needed to cope with transition issues. For example, Telstra bills have included back-dated GST amounts so the GST charged exceeds 10% and other bills span the changeover, and therefore the GST charged is less than 10%.

Other issues discussed were viruses, Excel techniques and conversion of old data files.

Our apologies but the address of the User Group's website has changed due to a reorganisation of servers by our Internet Service Provider TPGI. We are endeavouring to have the old URL work as well as the new, so that links will still work.

See the top of this page for the new address.

Microsoft should have read our August Newsletter. It turns out that their much-publicised security breach was probably caused by the QAZ Worm virus! See New Scientist 30 October 2000 (link on our website).

by Michael Paine

I had my first dabble with Excel 97 macros and Visual Basic programming the other day. It went remarkably well. It is like a cross between Open Access macros and macro editor and Open Access Programmer, with the extra power of Visual Basic. I found it much easier to use than the cumbersome Visual Basic system that comes with MS Access.

Recall that anywhere within Open Access you can start recording a macro (keystrokes) by pressing [Alt F8] and selecting CREATE. When you have finished the sequences of keystrokes press [Alt F8] again and select SAVE. You can then replay your macro at any time by pressing [Alt F8] and selecting EXECUTE. Better still you can repeat the macro numerous times by selecting REPEAT.

You can also edit Open Access macros by using the Macro Converter. Press [F8] and select Macro Converter. Select Macro to Text to generate a text file version of your macro. Then press [F8] to open the text file in Notepad. You can then edit the macro code (not an easy task), save the text file and convert it back to a macro.

A great facility and a nuisance that a similar facility is not available throughout Windows (Win3.1 had the very limited Recorder, but it was dropped when WIN95 was released).

Macros can be created within Excel 97/2000 in a similar way. First you need to enable the Visual Basic Toolbar (VIEW/TOOLBARS and check the Visual Basic box). Next place the cursor in the appropriate starting position then click on the New Macro button - as usual with Microsoft logic this is the filled-in circle

Give your new macro a name and click OK. The circle changes to a square! Now start your sequence of keystrokes. Mouse selections are also recorded, as are menu selections. When you have finished click on the button with the square. Your new macro can now be accessed from the Run Macro button (filled-in triangle!).

Editing the new macro is much easier than with Open Access. Simply select the macro from the list presented when you click on the Run Macro button and select Edit. The Visual Basic editing window pops up and your macro is display in (fairly) plain English.

The Visual Basic with Excel is quite easy to use, but there are some odd things to remember. In particular there is no command to address and work on a single cell. Instead you must select a "Range" consisting of one column and one row. For example Range ("B3:B3").

I had a problem when I wanted to split the values from one column according to categories specified in another column. Now you can do this with an IF formula, but I found a major difficulty plotting the subsequent results with a scatter (XY) graph. The problem is that an IF statement such as IF(K5="A",G5,"") puts either the desired value in the cell or a NULL value. Unfortunately, the null value is treated as a zero for XY charts and is plotted as zero. However, if the blank cells are CLEAR then they are ignored by the XY chart. I wanted them to be ignored (as there are in Open Access scatter graphs!) so I had to write a program/macro to achieve this. It worked well.

Below is some sample code. Note: the power of the statement "For Each Item in Selection". This steps through each cell in the selection and works on it using the code up to "Next Item". Note also the use of an offset value to pick the value from the G column. The relative addressing looks a little strange but Range("a1:a1") simply selects one cell at the offset location.

Public Sub split_speed()

' clear target area

Range("l3:n242").Clear 'This clears all cells in the range

' select the input range

Range("K3:K242").Select ' column K has A,C or D as a value

s1 = "text"

os = 0

For Each Item In Selection ' this steps through every cell in the

selected column

s1 = Trim(Item.Value)

os = 0 ' number of columns offset from selected column (K)

If s1 = "A" Then

os = 1 ' set offset value to 1

ElseIf s1 = "C" Then

os = 2

ElseIf s1 = "D" Then

os = 3

Else

os = 0

End If

If os > 0 Then

r1 = Item.Offset(0, -4).Range("a1:a1").Value ' read column G value. same row)

'the a1:a1 is odd but needed - it selects one cell at the relative address

Item.Offset(0, os).Range("a1:a1").Value = r1 ' set appropriate

column in output range to the value from col G

End If

s2 = s1

s3 = "X"

Next Item ' work on the next cell in the range

End Sub

There are more great tips in the book "Excel 97 Bible" by John Walkenbach. He also has a great suite of Excel utilities - the "Power Utility Pack". http://www.j-walk.com/ss

Report from Lindy about converting DAT data files (sequential files created by old versions of Basic).

ALT +SHIFT +D Insert Current Date

ALT +SHIFT +T Insert Current Time

Word has an option: ‘Semi-Transparent’ which allows existing text in the background to show faintly through the frame.

• Select the object to format
• Choose Format – Text Box or Object or WordArt – Colours & Lines
• Click in the Semi-Transparent check box next to Color
• Select the Wrapping tab and click desired wrap.

(Note the www4 and aoaug NOT aoaugh).