Caution

The comment does not assign the shortcut. If you change the comment to be Ctrl+j, it does not change the shortcut.This has to be done in the Macro dialog box from Excel.

Recorded macro code is usually pretty neat (see Figure 1.11). Each non-comment line of code is indented four characters. If a line is longer than 100 characters, the recorder breaks it into multiple lines and indents the lines an additional four characters. To continue a line of code, you type a space and an underscore at the end of the line. Note that the physical limitations of this book do not allow 100 characters on a single line. I will break the lines at 80 characters so that they fit on this page. Your recorded macro might look slightly different than the ones that appear here.

Figure 1.11

The recorded macro is neat looking and nicely indented.

Sub iBportlHVOiec [)

Keyboard Shortcut : Ctrl»]

f BUI Jelen Tliii

SorHxialES.Ûpcntexc rilenome:- _

»CsUiwolee-.t«", CulgiBi-ÎST, 3t«t*on:»l, ï(ttATypt = «*lDelimited.

Tenterai Hier;-it lEoubleQuote, Conuecut tve Del imiter ¡-Falae,

Tflj5!»Tr«r seaieoion!«!"»!«* Cotaioi-TEue, suce : -raise,

OtilCïi-rftlM, rietd(|4ÎO;»*«ie*V(*«*V(ilr «**Y(lr **<*Vl*r l(r _

Array 1), icray(5, 1), Array (6, 1), Array(7, 1]), Trail in jltimisHunfeers

Selection.LivllitlPDirnJ .Select Range {"AH*) .Select 4tt 11- roxm j *m çi ■ "'Total" Sange("ÉH") .Select

Selection, r«i*»lelllCl - "-SDlt C® r -1 SI C ; Rt -11 Cl "

Selection.AutoFill Ben t inat lan;-Ha&ie <"K 11 ; EH"), Typej-xirilirelault

Poage .Select

Sows("14:.Selecr select ioit.roat. 00 id » True

Cells.Select

Select ion.coiunna-AutoF it

Consider that the following seven lines of recorded code is actually only one line of code that has been broken down into seven lines for readability:

Workbooks.OpenText Filename:= _

MC:\invoice.txtM, Origin:=437, StartRow:=1, DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _ Tab:=True, Semicolon:=False, Comma:=True, Space:=False, _ Other:=False, FieldInfo:=Array(Array(1, 3), Array(2, 1), Array(3, 1), _ Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _ TrailingMinusNumbers:=True

Counting the above as one line, the macro recorder was able to record our 21-step process in 14 lines of code, which is pretty impressive.

0 0

Post a comment