[ xls ] VB Tips about VBA and macros in excel

1. From the Sheet, Right Click > Show Codes

2. The VB editor will be appears

TIPs for VB editor

– Go to definition ( Open Declaration )

right click > definition

Go back

right click > last position

– To prevent the screen swaping

Application.ScreenUpdating = False

or if we want to set it back

Application.ScreenUpdating = True

– Activate Worksheet


– Access Cell Values ( For example, if we want to access E4 cell


– MsgBox function


MsgBox(prompt(, buttons) (, title) (, helpfile, context))

prompt : string

buttons : can be vb MsgBox constanti.e. vbCritical

title : string

– Go To




On Error Go To XXX



– recordset

If Not rs Is Nothing Then rs.close



TIPs : record macro

1. View > Macros > Record Macro

2. do the event in excel ( i.e. save file as CSV )

3. View > Macros > Stop record Macro

The excel will generate the code for that action. The result will be in VBA modules.

TIPs : excel change column width and height

Home > Cells > Format

Insert Button

For MS 2010

Developer > Insert > ActiveX control > Button

Writing Excel macros

– save as .xls ( .xlsx does not contains all macro )

Option Explicit : force define variable ( This will be error at compile time. In VBA, we can check by `Debug > Compile VBA Project`.

On load worksheet and On Terminate Sheet

Note that the code is `Worksheet`, do not replace it with worksheet name !!!!

Private Sub Worksheet_Activate()
MsgBox ("Hi")
End Sub

Private Sub Worksheet_Deactivate()
MsgBox ("Bye")
End Sub

Protect VBA code

In VBA screen, Tools > VBAProject Properties

Protection tab
Tick Lock project for viewing
and set password


>> Values ( why ? )

communication : knowledge sharing, effective cooperation

simplicity : defer req. from today concern, reduce communication

feedback : reduce gap customer, developer; the sooner you know, the sooner you can adapt; Slow down released until u can handle defect reports; feedback is critical part of commu and contributes to simplicity;

courage : increase meaningful commu, discard failing sol and seek for new one -> simplicity

respect : no one can intrincically worth more than anyone else.

>> Principles ( How ? generally )

Humanity : XP meets personal needs; commu meets human needs for connection; rest, exercise, socialization;

Economics : produce biz value

Mutual benefit Activities should benefit all concerned;

doc –> automated test test-first programming
not benefit me now

refactor –> simplicity
coherent name

self similarity : reuse the sol that work, to Simplify design, less stress, more feedback

i.e. list the themes ( address stories ) -> list the stories ( address test ) -> list the tests

improvement : do your best today, better tomorrow; perfect(v) your process, design, stories; refine result overtime

diversity : team needs variety of skill, views; difference is opportunity, not a problem; ‘two ways to solve this’

reflection : how they are working/fail ? why they are working/fail ?; learn, analyze from errors.

flow : = deliver steady flow

opportunity : problem -> opportunity to change; learning + improvement, growth + deepening relationship;

redundancy : solve problem in multiple ways; defects cannot be solved with single process

failure : failure -> valuable knowledge

quality : is not an excuse for inaction; quality is not economic factor.

baby steps : small, safe steps; In term of change, overhead less than recovery from large step

accepted responsibility solving problem, not just complete the list of task

responsibility accepted, not assigned; with responsibility comes authority.
reduce misalignment between actions and consequence

>> Practices ( How ? specifically )

> Planning
weekly cycle : reduce time spent on planning, planning is a form of necessary waste.

; task create sense of ownership

; take task top of stack –> variety of task

quarterly cycle : bottleneck repairs, theme
— sync with other biz activity

slack ( เฉื่อย ) : include slack time; maintain trust for commitment

stories : customer-visible unit of functionality;

estimate time to implement;

priority req

> Programming and integration
energized work : work reasonable hours. prevent burnout;
remove distraction for a period. i.e. email, phone, chat

incremental design
design system every day
small, safe steps
keep cost of change low

test-first programming
keep test small but meaningful
avoid scope creep
loosely coupled, highly cohesive code is easy to test

maintain trust in code when pass to another person

continuous testing

ten minute build
– automatically build and run test in 10 minutes.
– easier, less stressful because it is automatically

continuous integration
– integrate + test change – hourly
– synchronous(integrate after each pair-programming), asynchronous(daily build)
– if there are problem, notify by email

> Team work
pair programming :
– rotate parter frequenly _ every hour to ส่งต่อ knowledge
– respect personal space
– maintain pace when one person stuck
– if wanna work with own idea(not code) alone, do it then come back and check with team.

sit together :
– open space i.e. conference room
– most problems resolved by interaction

whole team :
– maintain cross-functional team ( all skill & perspective )
– build sense of team ( we belong; we support each other, growth and learn
– expert in team when needed

– 12 is no. of ppl who can interact in a day
– fracture –> team of team

informative workspace :
– stories on a wall
– big visible chart
– external memory
– observer get general idea in 15 s.
– active information –> stop getting update -> take it down

>> getting start
— one step at a time

>> Corollary Practices

> Programming

single code base : you can develop temporary branch, but never let it live longer than an hour.

shared code : Anyone can improve any part of system any time. collective ownership.

code & test : as a permanent artifact. generate other docs from the code and test.


negotiated scope contact : To reduce risk, sign short contact instead of long one

pay-per-use : the team know the number of customer that continue to subscribe.

Pay-per-release reduce communication and feedback.

daily deployment : put new SW into production every night. Deployment tools must be automate.( roll out and roll back in case of failure)

> Team

team continuity : work with those they know and trust

shrinking(ทำให้เล็กลง) teams : The fifth person might work 30% of the time, so he can think about how to improve their work process.

until some of the team member are idle, then shrink the team and continue.

root cause analysis : the team will never make same mistake again. five why. ( almost always a people problem )

process for responding to defect

1. automated system level test

2. unit test

3. fix system so the unit test work

4. figure out why the defect was created and wasn’t caught. Initiate the necessary changes to prevent this kind of defect in the future.

real customer involvement : reduce waste effort

incremental deployment : Big deployments have a high risk, high human and economic costs

>> The Whole XP team

Testers : write automated test.

The role shift earlier, help define and specify what will constitue acceptible function before implemented.

Once tests for the week are written and failing, testers continue to write new test.

Interaction Designer : work with customers, help to write and clarify stories.

Architect : system-level tests that stress architecture. improve stress test until system broke. partition system.

Project Manager : coordinating communication with customer, supplier, executive and the rest of org.

Increase cohesiveness and confident. remind the team how much progress it has made.

Product Manager : pick themes and stories in the quarterly cycle. make sure that customer concerns are acted on the team.

Executive : encouraging, face of criticism, prepared the firm

Technical Writer : 1. provide early feedback about features.  2. create closer relationships with users. address user confusion.

This week stories can be next week’s documentation tasks.

If user never look at doc, stop writing it.

document usage added to tracking.

Users : help write and pick stories.

strong relationships with the larger user community

keep in mind that they are speaking for an entire community.

Programmers : estimate stories and tasks, break stories into task

HR : 1. reviews

individual goals, individual performance

valueable employee

– act respectful

– play well with others

– take initiative

– deliver on their commitment

2. Hiring more social candidate.

>> The theory of constraint

find the constraint : make sure it is working full speed.

Work is pulled based on actual demand, not pushed based on project demand.

>> Managing Scope

Native model

time, quality (sponsor)

, price(team)

>> Testing Early, often, and automated

– Pair programming — reduce defect

– two principle to increase cost-effective

1. double-checking two distinct thought processes at the same ans

One set is written from the perspective of programmer, another set is written from the perspective of customer.

2. Defect Cost increase ( DCI ) the sooner you find a defect, the cheaper it is to fix it.

>> Designing: The value of time

design up-front instinct

when design is not clear – thought

incremental design – experience


thought create more value — design sooner

exp create more value — design just enough for today