[Year 12 IT Apps] Macros in Excel

Thu Jul 26 11:09:32 EST 2007

Cool!

Another macro story...

Back in the days of CATs in IPM I wanted my kids to tackle the problem 
of creating a spreadsheet that could handle any number of records (rows) 
and also provide column totals.

They tried filling the formulas down N rows and putting the totals' 
formulas underneath, but realised this made the number of records 
finite.  So they put the totals at the _top_ under the column headers.

There remained the problem of a whole lot of formulas with no data to 
work with, leading to problems like ugly "NA" and "divide by zero" 
errors in cells.

That's when they realised a macro would be useful.

They removed the unneeded rows and created a macro that:

- copied the existing data rows.
- moved them all _down_ one row.
- copied the first row of data.
- pasted it into the newly-vacated row at the _top_ of the sheet.  They 
didn't add the new row to the bottom of the sheet because they would 
have to work out which was the last row: rather tricky.
- in the newly-created row, the macro deleted the cells containing data 
(like name etc), leaving the formulas.
- left the cursor sitting in the first data cell ready for input.

All they had to do then was create a button for the macro (labelled 'Add 
a person') and Roberta's your auntie.

Ah, the good old CAT days when we had serious time to follow a real 
challenge!

Mark K

Graham wrote:
> G'day
> 
> In relation to Macros:
> 
> As the timetabler, I have a copy of the timetable in Excel, one day per 
> worksheet.  I have a macro that will start at Monday's sheet, print 
> Monday's timetable, go to Tuesday's worksheet, print it and so on down 
> to Friday, and then return to Monday. (Yes I could have them all on the 
> one sheet, but this works better for me for formatting reasons.)
> 
> In relation to student work, I once had a student SAC that (from memory) 
> used one macro to
> 
>    1. Collect salary data for an employee by copying it from relevant 
> worksheets
>    2. Paste that data into a Payslip worksheet
>    3. Remove the colour (for prin! ting purposes)
>    4. Print the payslip
>    5. Copy the data
>    6. Open a new workbook
>    7. Paste the data
>    8. Save the workbook using employee name and the date 
> (070729Hughes_G.xls) 
>    9. Close this workbook
>  10. Return to original workbook
>  11. Clear the data cells on Payslip worksheet
>  12. Collect data about next employee
>  13. Repeat steps 2 - 11 until all employees were done
> 
> This provided a payslip for each employee plus a saved copy of each 
> paysjip for the business.
> 
> Once the above macro was completed, a second macro was run.  It 
> initiated a ' Save as' for the original workbook, saving it using the 
> date as the file name e.g. 070729Pays.xls.  This was closed and then the 
> original workbook was re-opened all the data cleared, readying it for 
> the next week's dat! a, and then it was saved.
> 
> I think that the above is accurate ... apologies if my memory is faulty ....
> 
> On a personal note, I, in response to a challenge from my class, once 
> wrote a macro that
> 
>    1.  Copied a weekly salary figure from a worksheet
>    2.  Pasted it in to a worksheet, 'Conversion',  that used a complex 
> series of formulae to convert the number to words
>    3.  Went to a worksheet called 'Cheque' and pasted, individually, the 
> salary figure in numbers and words into a cheque
>    4.  Retrieved the employee's name and pasted it into 'Cheque' in the 
> appropriate place
>    5.  Printed the cheque ready for signature (date was done using a 
> function).  I tried but did not succeed in getting the cheque numbers to 
> increment after each printing.
>    6.  Cleared the data from 'Conversion' and  'Cheque', ready for the 
> next employee
> 
> 
> I hope this is useful ... 
> 
> 
> 
> Graham Gales
> Bright P-12 College
> - - - - - - - - - - - - - - - - - - - 
> ITA, Science and Maths Teacher
> Timetabler/ Daily Organiser
> Gifted & Talented Co-ordinator
> Heir apparent to the Chief Cook and BottleWasher
> Understudy to the general Dogsbody
> 
> 
> On 25/07/2007, at 3:37 PM, Russell Edwards wrote:
> 
>> Does anyone have any thoughts on common uses of macros in Excel?
>>
>> I am struggling to come up with any examples that are generally 
>> applicable, and therefore worth putting in to student exercises.    I 
>> use macros occasionally but they are generally quite specific to the 
>> particular job that spreadsheet is designed for.
>>
>> About all I can think of would be setting the active cell to the first 
>> input data cell upon opening, and inter-sheet navigation buttons.
>>
>> thanks in advance
>>
>> Russell
>> Whittlesea SC
>>
> 
> *Important - *This email and any attachments may be confidential. If 
> received in error, please contact us and ! delete all copies. Before 
> opening or using attachments check them for viruses and defects. 
> Regardless of any loss, damage or consequence, whether caused by the 
> negligence of the sender or not, resulting directly or indirectly from 
> the use of any attached files our liability is limited to resupplying 
> any affected attachments. Any representations or opinions expressed are 
> those of the individual sender, and not necessarily those of the 
> Department of Education.
> 
> _______________________________________________
> http://www.edulists.com.au <http://www.edulists.com.au> - FAQ, 
> resources, subscribe, unsubscribe
> IT Applications Mailing List kindly supported by
> http://www.vcaa.vic.edu.auvce/studies/infotech/infotechindex.html 
> <http://www.vcaa.vic.edu.auvce/studies/infotech/itapplications3-4.html > 
> - Victorian Curriculum and Assessment Authority and
> http://www.vitta.org.au <http://www.vitta.org.au> - VITTA Victorian 
> Information Technology Teachers Association Inc

-- 
Mark Kelly
Manager - Information Systems
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: 8520 9085
School Phone +613 8520 9000
School Fax +613 95789253
kel AT mckinnonsc.vic.edu.au

Webmaster - http://www.mckinnonsc.vic.edu.au
IT Lecture notes: http://vceit.com
Moderator: IT Applications Mailing List

It's 'i' before 'e' except after 'c'.
Spelling is not an art, it's a sceince.