HowtoBeaProgrammerProgrammingBasicsMohanPalletiCopyright©2012AuthorNameAllrightsreserved.

ISBN-10:1490915753ISBN-13:978-1490915753FOREWORD

Thegoalofthisbookistobearesourceforinstructorseducatingstudentsorindividualsthatwanttolearncomputerprogramming.Computerprogrammingisafunandrewardingskillthatteachescriticalandlogicalthinking.Programmingcanbetaughttoanypersonwithbasicarithmeticandlogicalskills.Withthebasicprogrammingskillslearnedfromthisbook,studentswillbeabletoadvancetoahigherlevelofprogrammingontheirown..CONTENTS

Acknowledgmentsi1Introduction12BasicHousekeepingrulesPg4a.UnderstandingDataTypesb.Additionc.Subtractiond.Multiplicatione.Division3MSExcelPg17a.Exerciseb.Arithmeticincomputersc.SimpletoolsinMSExcel4ProgrammingusingMSExcelPg34a.Initializingb.CreatingyourfirstGUIHowtoBeaProgrammer-ProgrammingBasics

5LogicalProgrammingPg60a.Doloopb.Ifthenstatement

6ManipulatingdatausingPg67VBA(VisualBasicforApplications)7MorereadingPg92v

ACKNOWLEDGMENTS

Idedicatethisbooktomyendearingwife,Rajaniandfamilyfortheirconstantsupport.Andalso!Can’tforgetmydog,Bud..i

1INTRODUCTION

AreyouamongoneofthosewhodonotknowtheABC’sofprogramming,andyetatonepointinyourlife,youfeltagreatdesiretolearnsoftwareprogramming,butdidnotknowwheretobegin?

Donotgetdisheartened!Youarenotalone!Mostpeoplethinkyouneedtogetadegreein

Computerscienceengineeringtobeaprogrammer,andthatprogrammingrequiresanextensivemathematicsbackground.

Thetruth,however,isverydifferent.Anytypeofformaleducationwouldbehelpful,butasanewbie,youdonothavetoknowcomputerprogrammingoradvancedmathematics.Youcanlearnthebasicsfromthisbookandthenmigratetootherprogramminglanguagesbywhicheveravenueyouchoose.

JohnHoltisafriendofminewhoImetonthesubwayinMohanPalleti

NewYorkCitymanyyearsagowhenIusedtolivethere.Themorningwemet,Iwasinthesubwaygoingtoworkcarryingaheavyprogramminglanguagemanualundermyarms.

Thisisthehallmarkofaprogrammer.He/shedoesn’treadbestsellersormagazinesinhis/herfreetime.Insteadaprogrammerreadsprogramminglanguagebooksduringhis/herfreetime.Hismindisalwaysinaloop.Hecanneverleavehisscriptbehindattheoffice;alwaysthinkingthroughendlessmazes;howcanImaketheapplicationwork?AmImissingsomething?

Iamwarningyouthisishowyoumightendupinlife.ButmostofallIamwarningyoutokeepaneyeonthesepeople.Theycouldbumpintoyouifyouarenotwatching.

ComingbacktoJohn,“hewaslookingatmeandmybookandwashopingtocatchmyattentiontostartaconversation.Hefinallymanagedtogetmyattentionwhenhesaid“man,thatisabigfatbookyougotthere”hesaid.Ismiledandsaid,“yesIuseittodomyarm-presses.”Wesharedalaugh.

Johncontinuedtosay,“Youknow,ifIcoulddosomethinglikethatinmyoffice,itwouldmakemeaheroatmyworkplace.Asamatteroffact,thereisthisguyinmyofficewhoknowssomuchaboutthesecomputerthingsthateveryoneknowshimatwork.Heiscoolandhandsometoo.”

Thatmademethink-ifIcanwriteasimplebookthatwouldhelpeveryonenewtoprogramming,beabletoprogramwithouthavingtodownloadhundredsofdifferentsoftwareplug-insandupgrades,IwouldbeabletohelpmanyaJohnHoltbecomeworkplaceheroesandcomeintothemainstreamofprogramming.

Whatdoyouneedtolearnthiscourse?Adesktoporalaptopcomputer,acopyofMicrosoftExcel,onegoodfingertopokeatthekeyboard,andtimeonyourhands!

Doyouneedtoknowadvancedmathematics?Noyoudonot!Youjustneedthebasics!Themostimportantskillnecessarytobeaprogrammeristounderstandlogic.Ifyouknowthat,thenyouaregoodtogo!2BASICHOUSEKEEPINGRULES

UnderstandingDataTypes

IntegernumbersAnynumberthatisawholenumber(meansanumberwithoutadecimalparttoit)iscalledaninteger.Forexample9,23,54,328etc..Theseareallintegers.

Realorfloatingpointnumbers

Anynumberthathasadecimalparttoitiscalleda‘Real’or‘floatingpoint’number.Forexample:2.34,42.389,197823.34578,etc.areallrealorfloatingpointnumbers.

VariablesAvariableisastringofcharactersyouchoosetostoreavalue(anumericalnumberorcharacters).

Letussayyounameavariablecalled‘myFirstNumber,’youcanthenassignanumbertoit,forexample300.7567.Letussayyoucreateasecondvariablecalled‘mySecondNumber’andyouassignthevalue200toit.

Wenowwanttoaddthetwovariablesandstoretheresulttoanothervariablecalled‘myResult’inthenextstep.

Addition:Howdoestheprogrammerwritethisincode?Hereare4linesofcodethatshoulddoit.

1.myFirstNumber=300.75672.mySecondNumber=2003.myResult=myFirstNumber+mySecondNumber4.PrintmyResult

Happeningatthecomputerlevel?

1.Theprogramlooksatthefirstline.Thenlooksattheleftofthe‘=’signandcreatesablackboxwiththelabel‘myFirstNumber’.Thenitlooksattherightofthe‘=’sign;picksthenumericalvalueof300.767;storesitinsidetheboxwiththelabel‘myFirstNumber’.Intechnicaltermswesaythatavalueof300.767isassignedtothevariableontheleftside.Inourcasewenameditas‘myFirstNumber’

2.Theprogramnowlooksatthesecondlineandsimilarlycreatesavariable‘mySecondNumber’andassignsavalueof200toit.

3.Theprogramnowreadsthethirdlineofcodeandcreatesavariablecalled‘myResult’addsthevaluesstoredinthevariables‘myFirstNumber’and‘mySecondNumber’andstorestheresultinthevariable‘myResult’

4.Line3ofcodeprintsthevalueof‘myResult’whichis500.767Thatwaseasy,wasn’tit?

Beforeyoucreateavariable,youshoulddefineitsproperties.Howlargeareyournumbersinthedataset?Accordinglywewillallocatememorysizeforthevariables.

NumberswithdecimalfractionalpartsarecalledFloatingpointsandshouldbestoredaseitherSingleorDouble.

TheSingledatatyperequires4bytesofmemoryandcanstorenegativevaluesbetween-3.402823x1038and1.401298x10-45andpositivevaluesbetween1.401298x10-45and3.402823x1038.

TheDoubledatatyperequires8bytesofmemoryandcanstorenegativevaluesbetween-1.79769313486232x10308and-4.94065645841247x10-324andpositivevaluesbetween4.94065645841247x10-324and1.79769313486232x10308.

Inourexamplewehave2numbers.Thehighestnumberis300.767,whichisarealnumberandcanbestoredastype‘Single’.Thesecondnumberis200.WecaneithersaveitasaByteorasaninteger.

Asforthevariable’myresult,’itisuptoustodecidehowtheresultsaregoingtobestored.Ifyouonlywanttheintegerpartandgetridofthedecimalfractions,thencreatethevariable‘myresult,’asanInteger.Ifyouwanttheresulttostorethecompletenumberwiththedecimals,thenyoushouldstorethevariableas‘Single.’Nowwewillrewritetheprogramwithsomeveryslightchanges.

1.DimmyFirstNumberassingle2.DimmySecondNumberasint3.DimmyResultasint4.myFirstNumber=300.75675.mySecondNumber=2006.myResult=myFirstNumber+mySecondNumber7.PrintmyResult

ThewordDimbeforeavariablenameindicatesthatyouaredeclaringthedimensiontypeofthevariable.

Line1indicatesthatyouhavecreatedavariablecalled‘myFirstNumber’andyouhavedeclareditastype’Single.’

RemembertheSingledatatyperequires4bytesofmemoryandcanstorenegativevaluesbetween-3.402823x1038and-1.401298x10-45andpositivevaluesbetween1.401298x10-45and3.402823x1038.

Fornowwewillstorethevalue300.7567inthisvariable(aspercodeinline4)Thesecondlineofcodestatesthatyouhavecreatedavariablecalled‘mySecondNumber’asint.

‘int’isshortforintegerandthatishowtheVBAcompilerunderstandsit.IntegerDataTypecanholdvaluesbetween-2147483648through2147483647.ABytecanstorevaluesbetween0and255.

Nowyoucanstorevaluesintherange-32,768and32,767inthisvariable.Wewillstorethevalue200init(aspercodeinline5)

BecausemyResulthasdimensiontype‘int,’theresultwillprintonlytheintegervalue.Inourcase‘500’Question:IfmyResultwasdeclaredastype‘Single’thenwhatwouldhavebeenmyresult?Answer:500.7567Subtraction

ThesymbolforSubtractionis‘-’.Thisisthesameasthesymbolwelearnedinschool.LookattheExamplecodebelow:

1.DimmyFirstNumberassingle2.DimmySecondNumberasint3.DimmyResultasint

4.myFirstNumber=3005.mySecondNumber=2006.myResult=myFirstNumber-mySecondNumber7.PrintmyResult

Question:WhatisthevaluestoredinmyResult?Answer:100Multiplication:

Thesymbolformultiplicationinprogrammingis‘*’.Insteadofthemultiplicationsymbol‘X’thatwelearnedinschool,weshoulduse‘*’betweenthetwooperands.1.DimmyFirstNumberassingle2.DimmySecondNumberasint3.DimmyResultasint4.myFirstNumber=3005.mySecondNumber=2006.myResult=myFirstNumber*mySecondNumber7.PrintmyResult

Question:WhatisthevaluestoredinmyResult?Answer:60000Division:

Thesymbolfordivisionis‘/’.Insteadofthedivisionsymbol‘÷’thatwelearnedinschool,weshoulduse‘/’betweenthetwooperandsorvariables.

The‘/’iscalledtheoperatorandthenumbersoneithersideofitarecalledtheoperands.Inourcodeexamplebelow,wedonotusethenumbers(300and20)directlyforthedivision.Insteadwewillstorethemintwodifferentvariables(myFirstNumberandmySecondNumber).

1.DimmyFirstNumberassingle2.DimmySecondNumberasint3.DimmyResultasint4.myFirstNumber=3005.mySecondNumber=206.myResult=myFirstNumber/mySecondNumber7.PrintmyResult

Question:WhatisthevaluestoredinmyResult?Answer:15Sofarwehavelearnedhowtoplaywithnumbers,tostoreandmanipulatethem.Whatifwewantedtostoresometextsinthevariablesinsteadofnumbers?

ForexamplewewanttodesignaGraphicalUserInterface(GUI),whereauserentershisfirstnameandlastnameintwodifferenttextboxes.Youwanttograbthemandconstructapersonalizedgreeting.Thatiseasy!

FirstletuslearnhowtostoretextsinVBA.Atextisstoredinavariableasa‘string.’

1.DimFname,Lname,myGreetingasstring2.Fname=‘John’3.Lname=‘Holt’4.myGreeting=“HelloFriendlyNeighbor”&Fname&““&LnameQuestion:Whatisstoredinthevariable‘myGreeting?’

Answer:HelloFriendlyNeighborJohnHoltIwentaheadandwrotethecode.Don’tpanic-Iamgoingtoexplaineveryline.

ThefirstlinecreatedthreevariablesFname,LnameandmyGreetingallfollowedby‘,‘(wecanchooseanynameforourvariables).Theyareallofstringtype.Astringtypevariablecanstoreabout65535orsocharacters.Mostpeopleneveruseittostoremorethan100characters.

Thesecondlineofcodeputastringofcharactersthatread‘John’inthevariableFname.Line3assignsthecharacterstring‘Holt’tothevariableLnameLine4takesabunchofstring’sandjoinsthemusingan‘&’operator.

Letusseewhatstringswearejoining.Someofthestringsinline4havetoberetrievedfromthevariableswheretheyarestored,whileotherslike“HelloFriendlyNeighbor”and“”arenotstoredinanyvariables.

AsaresultmyGreetingnowstoresthejoinedstrings“HelloFriendlyNeighborJohnHolt”

ConcatenateJoiningmultiplestringsintoonestring.

Forexample“Mr.”&“John”&“Holt”whenjoinedtogetherbecome“Mr.JohnHolt.”NoticethatIhaddeliberatelyprovidedasinglespacebeforeandafterJohninthequotestomakeitreadable.Ifthespacewasnotprovideditwouldhaveread“MrJohnHolt.”

Stringmanipulation

ThereareseveralfunctionsavailableinVBAtomanipulatestrings.Wewillpickafewofthemanddescribetheformatsofhowtheyaretobeusedandthenwewillusetheminanexample.

Midfunction(Carvesoutanewsubsetofstringfromabiggerstring)Usage:MID(text,start_position,number_of_characters)

1.DimAword,NewWordasstring2.Aword=“Thenightwolfisontheprowl”3.NewWord=Mid(Aword,11,4)

Question:WhatdoesthevariableNewWordcontain?Answer:‘wolf’Inline1wecreatedtwovariables‘Aword’and‘NewWord’astypestrings.

Inline2westoredalineofcharacterstrings,“Thenightwolfisontheprowl”inthevariable‘Aword’Awordnowhas30charactersstoredinit.Character#1is‘T’Character#2is‘h’andsoon.

Inline3anewsetofstringiscarvedoutfromthecontentsstoredinthevariable‘Aword’Thenewstringreads4charactersstartingfromthe11thcharacterorpositionasshownaboveinhighlights.Ablankspaceisalsocountedasacharacter.

Thefourcharactershererepresenttheword‘wolf’.

LENFunction

(Countsthetotalnumberofcharactersinastring)Usage:LEN(Text)

4.Dimvlengthasint5.vlength=Len(Aword)Question:Whatdoeswlengthcontain?Answer:30

6.Thefunctionpicksupthestringfromthevariableinsidetheparenthesis;calculatesthelengthoftheentirestring;transfersthevaluetothevariableontheleftsideofthe‘=’sign.InourcaseAwordhasthestring“Thenightwolfisontheprowl”storedorassignedtoit.Theentirelengthofthisstringis30(includingthespacesbetweenthequotes).

RightFunction(Carvesoutthelastfewcharactersofagivenstringthelengthofwhichisdefinedbytheuser)Last3charactersUsage:RIGHT(Text,[numberofcharacters])7.Dimword1asstring8.word1=Right(Aword,3)Question:Whatdoesthevariableword1contain?Answer:‘owl’Thefunctionpicksupthelast3charactersofthestring“Thenightwolfisontheprowl”andassignsittothevariable‘word1’.

LeftfunctionUsage:Left(Text,[numberofcharacters])

(Carvesoutthefirstfewcharactersofagivenstringthelengthofwhichisdefinedbytheuser)9.Dimword2asstring10.Word2=left(Aword,9)

Question:WhatdoesthevariableWord2contain?Answer:‘Thenight’

Thefunctionpicksupthefirst9charactersofthestring“Thenightwolfisontheprowl”andassignsittothevariable‘word2’.3MSEXCEL

Letusdoanexercisetomakemypointsclear.StringmanipulationsusingExcel:

1.OpenupanExcelpage.YouseeanemptytablewithcolumnsdenotedbyA,B,C….andrownumbersontheleftstartingwith1.

2.Writethetext“Thenightwolfisontheprowl”inthecellA1withoutthequotes.AcellisalwaysidentifiedbyitsColumnIDandRownumber.CellA1MeansthecellatColumnAandRow1.

3.Expandthecellsothatwecanseetheentiretext.ThisisdonebyputtingthecursorbetweentheHeadercellsA&BanddoubleclickingitasinFig1.

4.ClickoncellD1andenterthisformulaonthefunctionbar=mid(A1,10,5)asinFig1.5.Seehowitextractstheword‘wolf’fromthetextstoredintheA1cellandputsitintothe

cellD1asin(Fig1).

6.ClickoncellE1andenterthisformulaonthefunctionbar=len(A1)7.SeehowthetotallengthofthestringinCellA1iscalculatedandprovidedatcellE1asinFig2.

8.ClickoncellF1andenterthisformulaonthefunctionbar=Right(A1,3)9.ClickoncellF1andenterthisformulaonthefunctionbar=Right(A1,3)10.SeehowitpickedupthelastthreecharactersontheextremerightandwroteitinCellF1asinFig3.

11.ClickoncellG1andenterthisformulaonthefunctionbar“=Left(A1,9)”12.Seehowitpickedupthefirst9charactersfromtheextremeleftandwroteitinCellG1asinFig3a.

NumericalmanipulationsusingExcel:Problem1

D’MacyandD’JCParetwolargechainsthatsellthesameperfumeEste-La-Noirintwodifferentsizeperfumebottles.Ourgoalistoseewhohasabetterprice.

D’Macysellstheperfumeinan8ozbottlefor$115D’JCPsellsthesameperfumeina5ozbottlefor$70CreatetheExcelfiletofittheabovedata.

Weneedtofindthecostperozateachofthesechains.

LetuscreateaheadingalsocalledafieldnameinCellD1;wewillcallitCostperoz.SeeFig3.2

ThecostperozatDMacy’scanbefoundbydividingthecostofthebottlebyitscontainingbottlesize,whichis140dividedby8.ThisisstoredinCellsC2andB2respectively.

ClickonCellD2andwritetheequation‘=C2/B2’asshowninFig3.2andpressenteron

thekeyboard.

Similarlywritetheequation(withoutthequotesofcourse)‘=c3/b3’incellD3andpressenteronthekeyboard.SeeFig3.3

TheotheroptionistocopytheequationinCellD2andpasteinCellD3.Thiscanbedonebyclickingtherightbuttononyourmouseandselectingtheoptionstocopyorpaste.YoumayalsoclickthebottomrightcornerofCellD2anddragitbelowtocellD3.ThecellintelligentlycopiestheequationbutreplaceswiththerespectivecellID’s.SeeFig3.4

Question:BywhatpercentageisD’JCPlessexpensivethanD’Macy?

Answer:20%.SeeFig3.5forexplanation.

Explanation:NoticetheformulaincellE3‘=(100*(D2-D3))/D2’

FirstfindhowcheapisD’JCP?BysubtractingthepriceperozatD’MacyandD’JCP(D2-D3).Theparenthesisensuresthatyousubtractthemfirst.

D3))NowyoudividetheresultbythecostatD’Macy(D2)andstoretheresultatcellE3

Problem2

Belowarethefirstweek’saveragedaytemperaturereadingsatOckracokeIslandinNorthCarolinaforthemonthofMay.WehaveProfessorDuncanMcDonaldvisitingtheIslandfromScotland.HewantstoknowthetemperatureindegreesCentigradesothathecandecideifheneedstopackanywarmclothing.InhiscountrytheymeasuretemperatureinCentigrade.

ThetemperaturereadingswehaveforthedaysMay1-7(shownbelow)areindegreesFahrenheit.

62747268717082

TheequationforconversionbetweenFahrenheit(F)andCentigrade(C)isgivenbyC=(F-32)x(5÷9)FirstwecopythedatatoanExcelspreadsheetConverttheaboveformulaforcomputing.Theformula

nowisC=(F-32)*(5/9)WritetheaboveequationinCellC3.Theequationistobewrittenas‘=(B3-32)*(5/9)’

becauseCellB3containstheFahrenheit(F)temperatureforthe1stofMay.SeeFig4.1

NowcopytheequationinCellC3totherestofthecellsbelow(toCellC9)asshowninFig4.2

ClickonthecellsC3thruC9andformattoshowtheresultsin2decimalplacesbyclickingtherightbuttonandselecting‘FormatCells’asshowninFig4.3

Nextselectthe‘Number’optionandselectthedecimalplacesto‘2’asshowninFig4.4andclick‘OK’button.

Theresultsarenowshownintwodecimalplaces(seeFig4.5).

4PROGRAMMINGUSINGEXCEL

Initializing

Withtheknowledgethatyouhaveamassedinthepreviouschapters,itshouldbefairlyeasyforyoutofollowtherestofthechaptersonhowtobeaprogrammerwithouthavinganyprogrammingsoftwarewithyou.

YouhavebeenusingMSExcelspreadsheets,soyoudohavesomethingthatwecanuse.

MSExcelhasalimitedversionofVisualBasiccalledVBAthatisavailablebuthidinginthebackgroundforustouse.WearegoingtoleveragethattolearnhowtomakeourGUI’sandapplications.

TherearealotofthingsyoucandowithVisualBasicifyouhaveacopyofthesoftwarewithyou.Youcandesignyourapplicationandthenmakeitintoanexecutablefileandshareitaround.OntheotherhandwithVBAyoucanstilldosomerudimentaryprogramming,butwillnotbeabletoshareitasanexecutablefile.InsteadyouwillsendacopyofyourExcelfilethatcontainsyourprogram.

DeveloperToolOpenupanExcelspreadsheetandseeifyoucanspottheDevelopertoolontheTop-Bar.Ifyoudonotseethisbarthenwewillhavetoswitchiton.ClicktheOfficeButtononthetopleftcornerof

MicrosoftExcelandselectExcelOptions(Fig5.1).UnderPopulartab,checkthe“ShowdevelopertabintheRibbon”andselecttheDevelopercheckbox(Fig5.2).

1.NowyouwillseetheDeveloperTabontheTop-barasinFig5.3

2.ClickontheDeveloperTab;clickontheVisualBasicTabasinFig5.4

3.NowyougetaBlankpage(refertoFig5.5).

Congratulations!Nowyouarereadytogointoanewworldofprogramming.CreatingyourFirstGraphicalUserInterfaceFormLetustakeupaproblemandtrytocodeasolution!

IntheearlierchapterweopenedupaMSExcelspreadsheetandconvertedasetofnumbersinFahrenheittodegreesCentigrade.

InthisexercisewewillcreateaGUI(GraphicalUserInterface)whereausercanenterarandomtwodigitnumberandconvertitintocentigradeasshowninFig6.1

a.Whentheuserinitiatestheapplication,aninputboxshouldasktheusertoentertheirname.b.Nexttheapplicationshouldprompttheusertoenteranumber(temperature)inFahrenheit.c.TheapplicationshouldcalculatetheequivalentFahrenheitvalueandshowitinthe

secondbox;thisshouldrepeatasmanytimesastheuserinputsnewnumbersinthefirstBox.d.Iftheuserclicksonthe‘Exit’buttonthentheapplicationshouldsaythankyoutotheuserandexittheprogram.

1.ClickonTab‘Insert’andselect‘UserForm.’ThiswillinsertaUser-FormasshowninFig6.2

2.YoucanaddanyoftheobjectsfromthetoolboxtotheformandchangeitspropertiestochangeitslookandfeelusingthePropertieswindowslikeName,Caption,Font,Coloretc.asinFig6.3.Atthistime,wewillleavethepropertiesasitis.IncasetheToolboxisnotvisible,youmayclickonViewandthenselectToolboxoption.

3.ClickontheTextBoxbuttonintheExcelControlsToolboxasinFig6.2andclickanywhereontheformtoplaceacopyoftheTextBoxbuttonontheForm.

4.LetusnameitTxtBox1inthepropertiesboxbychangingtheNamepropertytoTxtBox1asshowninFig6.3.Youcouldhavegivenanynametoit.FornowwenameditasTxtBox1.Thiswillbetheboxwhereauserwillinputa2digitnumber.

5.SimilarlydrawanotherTextboxbelowitandnameitTxtBox2intheNameproperty.ThisboxshoulddisplaythecalculatedequivalentCentigradevalueoftheuserenteredvalueinTxtBox1.

Put3LabelboxesontheFormandchangetheirtextstoread ‘EnteraTemperatureinFahrenheit,’‘TheTemperatureinCentigradeis:’and‘Temperature

Converter’andplaceitaroundontheformsintherightlocationsasinFig6.4.Labelboxeshelpdescribefunctionalitiestotheuser.BydefaultthefirstlabelreadsasLabeI1YoumustchangethetextsbychangingthetextintheCaptionsproperty.

AsinFig6.4addtwocommandbuttonsontheform

andnameitcmdStartandcmdExitrespectivelybychangingthedefaultnamesintheNamePropertytags.SeeFig6.5.

ThebuttonnamedcmdStartshouldreadthewordStart.ThiscanbedonebytypingthetextStartintheCaptionProperty.SimilarlychangethesecondbuttonnamedcmdExittoreadthetextExitonthebutton.

Whentheapplicationisinitiated,theapplicationshouldasktheusertoenterhis/hernameandstorethenameinavariableforlateruse.

EverytimetheuserclicksonthebuttoncmdStart,theapplicationshouldreadthedataenteredintxtBox1andoutputtheconvertedresulttoTxtBox2.

WhenuserclicksonthebuttoncmdExitwewantittogiveamessagethankingtheuserforusingtheapplicationandthenallowingtheusertoexit.

Beforecontinuing,thisfileshouldbesavedasaMacroEnabledworkbook.Itisuptoyouwhereyouchoosetosavethisfileat.

OnceIsavethefile,Iwouldliketoseethelocationwheremyfileisstored.WhenweopenupExplorer,weseeonlythefilenamebutfortheprogramtoaccessit,wehavetoknowthecompletefilenamewhichincludesthefolderlocationaswell.

RightclickonthefilenameandchoosepropertiesasinFig7.0,andconstructthecompletefileaddress.

InmycasethefilenameismyFirstScript.xlsmanditislocatedatC:\MyFirstProject.ThecompletefilenameisthereforeC:\MyFirstProject\MyFirstScript.xlsm.

Note:YourcompletefilenameistheFilelocationfollowedbya\andthenyourfilename.Writedownyourcompletefilenameifitisdifferent

IfyouclickontherunbuttonasinFig7.1theapplicationisinitiatedandtheUser-FormisreadytogoasinFig7.2.Buttheapplicationdoesnothingbecausethecontrolsareyettobeprogrammed.ClickontheXtoexittheprogram(Fig7.2).

Letusbeginprogrammingthecontrolsnow.

InitializetheFormDoubleclickanywhereontheform.Youwillseethecodebelow:PrivateSubUserForm_Initialize()EndSubWhatitmeans:ItcreatesanemptySubProcedurebetweenthetwokeywordsPrivateSubandEndSub.ASubProcedureisataskthatwillbeactivatedeverytimeaneventoccurs.ThisSubProcedurebelongstothe‘UserForm’,i.e.theformyoujustcreated.

Whentheapplicationisinitializedtheformwillbetriggered(event).Youchoosewhenthecodeneedstobeexecuted.Thiscanbechosenbyselectinganyoneoftheoptionsonthetoprightofthepage(seeFig7.3).

Letussay,youwanttochangethistoadifferentevent.Youwanttofirearoutinetaskwhenuserclicksontheform.Thenfromthedropdowneventselectionlist,youshouldselectClick.ThiswillgiveyouanewemptysubroutinecalledSubUserForm_Click()

andendwithEndSub.

Fornowwewillcontinuewiththedefaultsubprocedure.

ThewordPrivateatthebeginningoftheSubindicatesthatanyvariableyoudeclarehereisvisibleonlytothissubprocedure.

ClickonUserform1undertheFolderFormsinProjectExplorerasshowninFig7.4togobacktoyourform.

Test:DoubleclickonthefirsttextboxcalledTxtBox1.YouwillnoticethatitwilltakeyoutoanemptysubroutineforTxtBox1_Change()wherethedefaulteventintelligentlypickedbytheapplicationisChange.Thismeans,theroutinetaskyouwanttheapplicationtodowhenauserinputsanewnumber,thusimplyingachangeevent.

Iwouldliketheprogramtopicktheuserinputnumberfromtxtbox1andfeedittoatemperatureconversionequationandthrowtheresulttothesecondtextboxcalledtxtBox2

BeginScriptingtomakeithappenInitializetheform

Whentheapplicationstartsforthefirsttime,theUserFormgetsinitialized.Wedoubleclickontheformtogotothecodepage.

YouwillseeanemptysubprocedurecalledPrivateSubUserForm_Click()thatendswithanEndSub.

YougotthisbecauseyoudoubleclickedontheformandthereforeitcreatedaUserForm_Clickevent.Leavethissectionalone.

OnthetopoftheoftheformclickthedropdownlistwindowandselectUserForm.Thisdropdownlistcontainsalistofalltheobjectsontheformandtheformitself.

Ontherighthandtopcornerofthecodewindowcontainsadropdownlistofalltheeventsthatcanpossiblytakeplace.SelectInitialize.

NowyougetaemptywrappercodeUserForm_Initialize()thatendswithanEndSub.

FillinthemissingcodefrombelowCodesample1

PrivateSubUserForm_Initialize()‘DimstrNameAsStringstrname=InputBox(Prompt=“Younameplease.”,_

Title:=“NameForm“,Default:=“YourNamehere”)EndSubCodesample1

1.ThispopsoutanInputBoxandpromptstheusertoenterhis/hernameasshowninFig8.1whentheprogramruns.ClosethisbyclickingontheXonthetoprightoftheform.

2.Whentheuserentershis/hernameontheformaboveitisstoredinavariablecalledstrnameIgaveitvariabletypestring.Ithenrealizedthatifthisvariableisdeclaredinthisprivatesubroutinethenotherbuttonsoutsidewillnotbeabletopickupthestoredvalueinthisvariable.Inourcasewewanttheapplicationtoremembertheuser’snameinavariableandhanditovertotheexitbuttonwhenclicked.ThentheExitbuttonwassupposedtosayThankyoutotheuser.

3.OnsecondthoughtsIcommentedthedimstatementbyaddinga’tothebeginningofthestatement.Thestatementlooksgreennow.Theapplicationprocessignoresallcomments.Ihavecopiedthestatementtothetopofthepageinthegeneralsection.Anyvariabledeclaredherecanbecalledbyanysubroutine.

CodingtheGeneralsectionAddtheDimstrNameAsStringatthetopofthepageinthegeneralsectionasinFig8.1.1

CodingcmdStartbuttonDoubleclickonthecmdStartbuttontogototheemptysubroutine.Writethecodeslines2thru6inCodesample2

PrivateSubcmdStart_Click()DimfNumberAsIntegertxtBox2.Text=”“fNumber=Val(TxtBox1.Text)txtBox2.Text=Str(fNumber-32)*(5/9)TxtBox1.Text=”“EndSub

Codesample2

1.FirstwedeclaredavariablefNumberasanintegerusingtheDimstatement(DimstandsforDimension)2.Astringisanynumberofcharactersbetweenquotes.Ifyouputablankspacebetweenthequotesthenyourstringcontainsjustanemptyspace.3.WewanttocleartxtBox2bywritingablankspaceinthebox.YoucaneithergotothetextpropertiesandsetitmanuallybyenteringablankspaceasinFig8.2

OrsetthetextBox2textparameterautomaticallybycodeasinline2txtBox2.Text=”“

4.WhenauserentersatemperaturevalueintxtBox1,thevalueisstoredasastringvaluebydefaultinavariablecalledTxtBox1.text.

5.TheValfunctionusageisgivenbyVal(ByValExpressionAsString)AnynumberpreviouslystoredasastringinavariablecanbeconvertedtoitsrealnumericalvaluebyputtingthevariableinparenthesiswiththeValfunctionontheleftoftheparenthesis.Whenauserinputsanumberviatheinputboxthevalueisstoredasawordorastring.Youcannotdoanythingwithanumberthatisstillinawordform.YouhavetousespecialfunctionsliketheValfunctiontoconvertitintoanumericalvalue.Tomakeitsimpleifyoutype2+3=inMicrosoftworditwon’tgiveyouavaluebecausethenumbersarereadaswords.Whereasifyoutype2+3=onacalculatoritgivesyouthevalue5becauseittakesthenumbersthatyoutypeandreadsthemasanumericalvalue.

6.fNumber=Val(TxtBox1.Text).InthislineofcodetheuserinputnumberstoredasastringbydefaultinthevariabletxtBox1.TextisconvertedtoanumericalintegervalueandstoredinthevariablefNumber.RememberthatwehavedeclaredfNumberas

Integer.ItcanonlystoreanIntegervalue.

7.txtBox2.Text=Str((fNumber-32)*(5/9))Inthiscode,Alloperationswithinparenthesisareperformedfirst.SotheintegernumberstoredinthevariablefNumberissubtractedby32;multiplyingtheresultwiththeresultfromdividing5by9;theresultwhichisobviouslyanotherintegernumberisnowconvertedtoastringvalueusingtheStrfunction;ThisstringvaluenowsetsthetextparameteroftxtBox2anddisplaysthevalueinthetext-box.Textboxescanonlydisplaystrings.Thereforeifyouhaveanumericalnumberwhichyouwanttodisplayinatextboxyouwillneedtoconvertittoastringtype.

TheStrfunctionusageisgivenbyStr(ByValNumberAsObject)

AnyvariablethathasanumberintegerorrealstoredinsideitcanbeconvertedtoastringifyouputitwithinaparenthesiswiththeStrfunctiontotheleftsideoftheparenthesis.

ConfusionaboutanumberasaStringorInteger?Iftwonumbers4&5arestoredinvariablesVar1andVar2asintegersthenVar1+Var2willadd4+5andgiveyouavalue9

Ifontheotherhandthesametwonumbers4&5arestoredinvariablesVar1andVar2asstringsthenboththesevariablescannotbeaddedormultipliedbecauseitisatext.

CodingthecmdExitbuttonDoubleclickonthecmdExitbuttonFillthemissingcodefrombelow

PrivateSubcmdExit_Click()MsgBoxstrname&”,Thankyouforusingmyapplication!”EndEndSub

CodeSample3

1.ThefirstlinetellsthatifauserclicksonthecmdExitbutton,thenexecutetherestofthecode.2.Line2usesthemessage-boxfunctiontopopoutamessage.ThemessagewillwritethevaluestoredinthevariablestrnameandjoinsthestringThankyouforusingmyapplication.

ForexampleiftheuserhadsignedinasMr.DuncanMcDonaldthenattheendoftheapplicationthesystemwillflashamessagestatingMr.DuncanMcDonald,Thankyouforusingmyapplication!

3.Line3signifiesthattheapplicationneedstobeterminated.

Nowruntheprogramandtesttheresults!Incaseyouhaveaproblem,looktoseethatthefollowingcodesarecorrectlyenteredDimstrnameAsString

PrivateSubcmdExit_Click()MsgBoxstrname&“,Thankyouforusingmyapplication!”EndEndSub

PrivateSubcmdStart_Click()DimfNumberAsIntegertxtBox2.Text=”“

fNumber=Val(TxtBox1.Text)txtBox2.Text=Str(fNumber-32)*(5/9)TxtBox1.Text=”“EndSub

PrivateSubUserForm_Initialize()‘DimstrNameAsStringstrname=InputBox(Prompt:=”Yournameplease.”,_Title:=“NameForm“,Default:=“YourNamehere”)EndSub

CodeSample4Nowisagoodtimetorunthisapplicationtoseewhatitlookslikeinpractice.Congratulations!YouhavemadeyourfirstGUI.

Butyouarenotfinishedyet.Ihavetoteachafewmorethingsthatwillstrengthenyourarsenalandmakeyouarealprogrammer.ButfromhereIamgoingtobebrieferinmydescriptions.Straighttothepoint,ifImay!5LOGICALPROGRAMMING

DoloopAsthenamesuggest,itisaloopandyouareaskingtheapplicationtorepeatdoingsomethingtillsomethinghappens.Wewilldealwiththiswhenwedothenextproject.

IfStatement

InCodeSample5givenbelowyoucaninsertaConditionaliftoallowausertoenterdatanomorethan5times.Afterthe5thtimeamessageshouldpopuptosayThankyouforusingmyapplication!Youmayrestartagain!SeeCodesample5.Thiswasdoneusingan‘If’tocontinuerepeatsomethinguntilaspecificcriterionismet.Clickanywhereontheformanddeleteallthepreviouscode.Copythecode(lines1-27)frombelowandpasteitwherethepreviouscodewaslocated1DimstrnameAsString2DimrecordNoAsInteger

3PrivateSubcmdExit_Click()4MsgBoxstrname&“,Thankyouforusingmyapplication!”5End6EndSub7PrivateSubcmdStart_Click()8DimfNumberAsInteger9recordNo=recordNo+110txtBox2.Text=”“11fNumber=Val(TxtBox1.Text)12IfrecordNo<5Then13txtBox2.Text=Str(fNumber-32)*(5/9)14TxtBox1.Text=”“15Else16MsgBox_17“Thankyouforusingmyapplication”18MsgBox”Youmayrestartagain!”

19EndIf20EndSub21PrivateSubUserForm_Initialize()22recordNo=023strname=InputBox(Prompt:=“EnterYourname.”_24,Title:=“NameForm“,Default:=“YourNamehere”)

25EndSubCodeSample5Codesexplained

1.Understandthatlinenumbershavebeenaddedforreadability.2.IfyoucopytheabovecodetouseinVisualBasic,makesureyoudonotaddthelinenumbers.3.InLinenumber2,wehaveintroducedaVariablerecordNooftypeInteger4.Inline12,weusedanIfStatementthatendsitsutilityinline19.5.LetusGoogletheusageofanIfstatement(thecorrectformatforusinganIfstatement).

Ifcondition[Then][statements][ElseIfelseifcondition[Then][elseifstatements]][Else[elsestatements]]EndIf-orIfconditionThen[statements][Else[elsestatements]]

Source:http://msdn.microsoft.com/enus/library/752y8abs(v=vs.80).aspx

6.Fromthelistofoptionsavailable,wechoosetheIfThenconditionfollowedbyanElseandanEndIfstatement.

Followthecode:

Whentheuserstartstheapplication,bydefaultwhateveryouhavescriptedinSubUserForm_Initialize()andthedimensionsofvariabletypesintheGeneralsectiongetsinitialized.Inourcase,twostringvariablesdeclaredinthegeneralsectionstrnameoftypeStringandrecordNooftypeintegerarecreated.Atthistimethevariablesdonotcontainanyvalues.

7.UserForminitialize()subroutinestoresavalue‘0’inthevariablerecordNo.Andthevariablestrnamestoreswhatevernametheusertypeswhenpromptedtoenterhisnameinline23and24.NoteLine23and24areactuallyonesinglelineofcode.Thecontinuationsymbol‘_’attheendofline23informsthecomputerprogramthatthenextlineisacontinuation*ofthisline.

8.Nexttheformgetsinitializedandpromptstheusertoenteravalueinthefirstboxandwaitstilltheuserclicksthestartbutton.

Line23and24canbewritteninonesinglelinewithoutthecontinuationsymbolifspacepermitasstrname=InputBox(Prompt:=“EnterYourname.”,Title:=“NameForm“,Default:=“YourNamehere”)

9.Whenthestartbuttonisclicked,itkick-startsthecmdStart_Click()subprocedure(lines7–20)

10.Notethatline11hasavariablerecordNowhichwas‘0’whenwefirststartedtheprogram.Aftereveryclickevent,recordNowillbeincrementedby1.

11.Line10blanksthetextintxtbox2withaspace(thecharacterbetweenthequoteisasinglespace).Thisactionclearsthetextbox.

12.txtBox2.TextreferstothetextstoredinthetextboxcalledtxtBox2.Bydefaultanythingtypedhereisstoredasatext(alsocalledastringincomputerlanguage).

13.Inline11thetextstoredintxtbox1isconvertedtoanumericalvalueusingtheValfunction.Notethatiftheuserhadentered‘fifty’youwillgetanerror.Youhavetotypethenumber‘50’initsnumericforminstead.

14.Line12checksforacondition.IfthevariablerecordNohasavaluelessthe5,thandothenextstatement,i.e.convertthevalueinfNumbertoaCentigradetemperaturevalueanddisplayitinthesecondtextbox(line13);nextblankoutthefirsttextboxbyaspacesothattheusercanenteranewvalue(line14).

15.Eachtimetheuserentersanewnumber,variablerecordNogetsincrementedbyonenumberandthenaslongasitsatisfiestheifcondition,itcalculatestheequivalentCentigradevaluesusingtheequationontherightsideofthe“=”signandassignsittothevariableontheleftside.ThisreferstothetexttobedisplayedintxtBox2ontheform.

16.Theuserisabletoenternewvaluesfourtimes(lessthan5).Onthe5thtimevariablerecordNobecomes5andthereforenolongersatisfiestheconditioninline12.Nowtheprogramjumpstoline15whichistheelsestatement.

17.Lines16-17popsoutamessageboxstatingThankyouforusingmyapplication!Afteryouclickokayyougetanothermessageboxfromline18thatsaysYoumayrestartagain!

18.ThiswholesequencewillrepeatagainunlessyouclickontheExitbuttontokickstartthecodesinlines3-6.

19.Line4popsoutaonelinemessagestatingtheuser’sname(thevaluestoredinthevariablestrname)followedbya‘,’andthemessageThankyouforusingmyapplication!Congratulationsyouhavelearnedhowtousealogical‘If’statement!

6MANIPULATINGDATAUSINGVBA(VISUALBASICFORAPPLICATION)Letusdoafinalprojectthatwillgiveyoumoreinsightonthebenefitsofprogramming.

Project:BobFentonworksatthefrontdeskofaChiropractor.Theofficehasatextfileintheirdesktopcomputerthatcontainsinformationaboutthecompany’sclients.

Whenacustomercallsin,basedonacustomer’scallerID,BobwantstobeabletoknowwhoiscallingandfindtheircorrespondingFileIDusingaGUI.

ThebestwaytodothatwouldbetotakethetelephonenumberfromthecallerIDandsearchthedatabaseintheirdesktopforamatch.Ifamatchisfound,hecouldpulluptherestoftheclientinformationliketheFileID,FirstNameandLastNameandshowitintheform,asshowninFig9.0.

Canhedothis?Yeshecan!Andletushelphimachieveit!Letustakeasmallchunkofhisdatabasefileandseewhatitlookslike(thenumbershavebeenchangedforsecurity)Hisdatabaseseemstohaveafixedformat.Thereare4itemsperlineandeachitemisseparatedbyacomma.ThevaluesareFile-ID,FirstName,LastNameandTelephoneNumber215,Mary,Barber,(919)382-4156216,Kimberly,Davis,(910)563-7181217,Jessica,Lowry,(919)342-7181218,Kristi,Goodwin,(919)782-3146219,Tavia,Bullock,(917)781-9132220,Tanneisha,Bryant,(212)879-2135221,Nakai,Abercrombie,(910)782-8193222,Chesare,Boeger,(919)782-6172223,Michelle,Bullock,(616)987-2137224,Beatrice,Davis,(732)231-8193225,Cerese,Barlow,(917)781-5162226,Candace,Jackson,(919)675-2118227,Geneva,Tally,(919)235-8193228,Paige,Cooper,(245)781-9117229,Staci,Newsome,(919)765-2136230,Maria,Black,(910)326-8179

Letuscopy&pastethisinnotepadandsaveitasatextfile*tomyCdriveat:C:\MyFirstProject\ClientDataandcallitcClient.txtasinFig9.1(createasimilarlocationonyourharddisk).Youmaydownloadacopyofallthecodesanddatafromwww.how-tobeaprogrammer.com

Whenyouright-clickonthefilenameinExplorerandselectproperties,itgivesyouthelocationofthefileinyourcomputerasinFig9.2.

Yourcompletefilenameisthelocationaddressfollowedbythefilename.LocationaddressisalsocalledthePath

cClient.txtisyourcustomerdatabasefileinatextformat.

Letuscreateaformwithatextboxwhereyoucantypeinaclient’stelephonenumber;openupthecustomerdatabasefile;searchlinebylineforamatchingtelephonenumberanddisplaythecorrespondingFirstname,LastnameandClientfilenumber.Alsostorethesameinformationintheexcelfiletolistoutthepeoplewhocalled.Ifamatchisnot

foundjustgiveamessagesayingNewCustomer!

1.IfExcelisopen,closeit.2.OpenanewExcelfile;savethefileasC:\MyFirstProject\clientListReader.xlsmasinfig9.3a.a.ClickontheMicrosoftOfficeButtonandselectthefileSaveasoption.b.BrowsetoyourC:\MyFirstProjectfolder.c.TypeyourfilenameclientListReaderintheFilenamebox.d.IntheSaveasTypeboxchooseExcelMacroEnabledWorkbook(*.xlsm)fromthedropdownlist.

3.ClickontheDevelopertab;clickonVisualBasic;nowyouareinMicrosoftVisualBasicpage.AsinFig9.3b

4.ClickInsert/UserFormasinFig9.4a.Thiswillinsertanewform.ResizeitbyselectingthehandlebarsontheedgeoftheformanddraggingittoanappropriatesizeasinFig9.4b

5.Weneed4Textboxes.WewillcallthemtxtPhoneN,txtFileN,txtFname,txtLname;wealsoneed4labelboxeswithappropriatetextstodescribetheTextboxes.LetusnamethemlblPhoneN,lblFileID,lblFname,lblLastN,(note:Textbox,Labelnames,commandbuttonnamesetc.donothavespacesintheirnames)withCaptionssettoCallerID,FileNumber,FirstNameandLastNamerespectively;weneed2CommandButtons.Onetostartthesearchandmatchprocessesandtheothertoexittheprogram.LetuscallthemcmdStartandcmdExit.NowsettheircaptionstoStartandExitrespectively.SeeFig9.5

6.DoubleclickontheformtocreateanemptysubroutinefortheUserform_Click();gobacktotheformbyclickingonUserform1underFormsinProjectExplorer.SeeFig9.6

7.Similarlywhenyoudoubleclickineachofthetextboxesorcommandbuttons,emptysubroutineswiththeirrespectiveeventactionsarecreated.8.ButwewilldeletethemallandcopyandpasteCodeSample6.9.SelectallthecodeasinFig9.7anddeleteit.

10.CopyandpasteCodesample6frombelowontheformCodewindow.1DimcallerIDAsString2DimmyLastRowAsLong3DimrecnoAsInteger4DimmyLastColumnAsLong

5

6PrivateSubcmdExit_Click()7End8EndSub9

10PrivateSubcmdStart_Click()11callerID=“(”&Mid(txtPhoneN.Text,1,3)&“)”_12&Mid(txtPhoneN.Text,4,3)&“-”&Mid(txtPhoneN.Text,7,4)

13

14CallTextMatchSearch15EndSub16

17SubTextMatchSearch()18DimFileID,FirstName,LastName,_19PhoneNumberAsString20DimmatchFound,lastrowAsInteger21‘VariablematchFoundiscreatedtotrackthestatus22‘ofatelephonenumber.search23‘IfamatchisfoundwewillsetmatchFoundto124‘elsewesetitto‘0’25matchFound=026‘Openthedatabasefileasaninputtoreadit.27‘Callthisconnectionas‘#1’

28Open“C:\MyFirstProject\ClientData\cclient.txt”_29ForInputAs#1

30

31‘Readtheabovefilelinebylinetilltheendofthe‘fileis32reached33WhileNotEOF(1)34‘Eachlinehasafixedformatof4itemsall35‘separatedbyacomma.36‘Feedthe4itemstothe4variables.37Input#1,FileID,FirstName,LastName,_38PhoneNumber39‘Ifamatchhasbeenfoundenterthissection40‘Lines41to6241IfcallerID=PhoneNumberThen42matchFound=143txtFileN.Text=FileID44txtFname.Text=FirstName45txtLname.Text=LastName46‘Callthe“myLastCell”function.47‘Thisfunctiontellstherecordnumber48‘ofthelastfilledcellinthecurrentExcelfile49‘afterwhichyoucanaddnewcustomerdata.50CallmyLastCell51‘Theapplicationreturnsbackwithcurrentvalues52‘for”myLastRow”

53

54‘Variablerecnocontainsthenextemptyrow55‘numberwhereyoucanaddnewdata.56recno=myLastRow+157Cells(recno,1).Value=recno58Cells(recno,2).Value=FileID59Cells(recno,3).Value=FirstName60Cells(recno,4).Value=LastName61txtPhoneN.Text=””62EndIf63‘Gobacktoline#40andrepeattheabovesteps64‘tillyoucompletereadingtheendofthefile65Wend66‘Ifamatchhasnotbeenfoundinourrecordsthen67‘dothis68IfmatchFound=0Then69‘clearoutthefourtextboxes70txtFileN.Text=””71txtFname.Text=””

72txtLname.Text=””73txtPhoneN.Text=””74MsgBox“NewCustomer!”75‘Callthe“myLastCell”functiontofindthelast76‘filledrownumberinyourExcelfile77

78CallmyLastCell79‘recnoconsiststhevalueofthenextemptyrow80‘intheExcelfile.81‘writethestring“New”“customer”incellsC&D82‘respectively.83recno=myLastRow+184Cells(recno,1).Value=recno85Cells(recno,2).Value=””86Cells(recno,3).Value=“New”87Cells(recno,4).Value=“Customer”88EndIf89Close(1)90EndSub91SubmyLastCell()92‘ThisisafunctionthatistaskedtoopenthecurrentExcel93‘sheetandfindoutthelastfilledrowandcolumn.94‘Whenyoucallthisfunction,itwillupdate‘myLastRow’and95‘myLastColumnwiththelastfilledrowandcolumnnumbers96‘of‘yourexcel‘sheet.9798

99Range(“A1”).Select100OnErrorResumeNext101myLastRow=Cells.Find(“*”,Range(“A1”)_102,xlFormulas,,xlByRows,xlPrevious)_103.Row104myLastColumn=Cells.Find(“*”,Range(“A1”)_105,xlFormulas,,xlByColumns,xlPrevious).Column106Cells(myLastRow,myLastColumn).Select107EndSub

108CodeSample6109110Ifyoumanuallytypeinthecode,rememberthat111allstatementsthatbeginwith’andisgreenin112colorarecommentstatementsthereforeneednot113becopied.114CodesExplained1.Whenyoulaterruntheapplication,ourformwouldlooklikeFig10.1below.

Theusertypesinatelephonenumberandclicksonthestartbutton.TheapplicationfiresthePrivateSubcmdStart_Click()subprocedure(lines10-15.)

2.Theuserenterednumber(e.g.:9193427181)istakenandreconstructedtoatelephonenumbertakenandreconstructedtoatelephonenumber7181)andstoresitinvariablecallerID.The10digitnumbersaresplitintothreeparts;parenthesisareinsertedtoholdthefirstthreedigits;aspacefollowedbythenextthreedigits;andahyphenfollowedbythenext4digits.

3.NextitseesthewordcallTextmatchSearch.(seeline14).Itlooksintheentireprogramtoseeifthereisafunctionorprocedurebythatname.Itdoesfindthefunctionbetweenlines17and90.

4.AFunction/Procedureislikeahelperwhosejobistodoaroutinejob.Forexampleyouareinanicecreamshop.Eachtimethefrontdesktakesaspecificorder.Hecallsouttothehelperwhofetchesthemade-to-orderice-creamanddisappearstillheiscalledagain.

5.ThefunctionTextmatchSearchisdesignedtopickare-constructedtelephonenumberandcompareitwitheachrecordinthedatabasetoseeifitcanfindamatchinthedatabasereadingitlinebylinetilltheendofthefile.IfitfindsamatchingnumberthenitwillpicktherestoftheclientinformationfromthedatabaseandpopulatetheGUI,elseitwillgiveamessagesayingNewCustomer.Inbothcasesitwillwritesomeclientinformationontheexcelfile.

6.Atlines18and20ithascreatedabunchofstringandintegervariables.

Rememberthatourdatabaseformathas4itemsperlineandeachitemisseparatedbyacomma.ThevaluesareFile-ID,FirstName,LastNameandTelephoneNumber.WearegoingtoreadthedatabaselinebylinetocomparewiththevaluesstoredinthevariablecallerIDsowewillneed4temporaryvariables.LetuscallthemFileID,FirstName,LastNameandPhoneNumbertoholdthemaswereadeachline.matchFoundisavariablethatwillhaveavalue1ifamatchisfoundanda0ifnomatchisfound,youwillseeaswegofurtherwhyweneedtokeepchangingthisvalue.

7.Atline28theprogramopensourdatabasetextfileC:\MyFirstProject\ClientData\cclient.txtasaninputforreadingandcallsthisconnectionChannel#1.

8.Rememberyouneedtochangethislineofcodetoyourcompletefilenameifitisdifferent9.Lines33to65createsaloop.Awhilestatementalwaysendswithawendstatement.

10.Line33saysthatwhilethefileopenedandnamedaschannel#1hasnotreachedtheEndoftheFile(EOF)conditionrepeatsomesteps.

Line37statesthatintheconnectionnamed#1,allthe4itemsthatarereadsequentiallyineachlineistemporarilystoredinthe4variables,FileID,FirstName,LastNameandPhoneNumber.Thesevariablesremainunchangedtilltheprogramfinishesonecycleatline65(thewendstatement);thenitreadsthenextlineofourdatafile;picksupthenextsetof4values;storesthemintheabovevariablesandsoon…

Beforethewendstatementisreached,howeverthereareasetoftaskstocomplete.Letuslookatthemsequentially…

11.Rememberthatinline11wehaveconstructedthetelephonenumberoftheclientandstoredtheminvariablecallerID

12.Inline41wearecheckingtoseeifthevaluesstoredinthevariablecallerIDmatcheswiththevaluesinvariablePhoneNumberreadfromthefirstlineofourdatafile.The‘If’statementstartsatline41andendsatline62.IfamatchisnotfoundthentheprogramjumpstotheWendstatementatline65andloopsbacktothebeginningofthewhilestatementatline33andreadsthenextlineofdata.ThisprocessrepeatstillallthelinesinourinputfileisreadandtheEndofFile(EOF)hasreached.

IfamatchisfoundthenwesetvariablematchFoundto1.Thisisonlyforourreference.Wecouldhavegivenitanyvalue!ThisisinLine42

PickupthevaluesinvariablesFileID,FirstName,LastNameanddisplaythemontheGUIformintextboxestxtFileN,txtFnameandtxtLnamerespectively(lines43-45).

13.Atline50theprogramseescallmyLastCellandItrecognizesthatthereisafunctionbythatname.14.Thentheprogramjumpstotheprocedureatline91andrunstheproceduretillitendsatline108;thisprocedurefindsthelastfilledrowinsheet1oftheExcelfile.

15.ThisisrequiredbecausetheprogramneedstoknowwheretheprogramshouldwritethenextlineofinformationintheExcelfile.

16.NoticethattwovariablesmyLastRowandmyLastColumnweresettocontainthelastfilledrowandthelastfilledColumnnumbers.Thesevariablesweredeclaredinthegeneralsectionatthetopoftheprogrampage.Thisvariableisthereforeavailabletoanysubproceduresorfunctionsthatwanttoreadthecurrentvaluesinthesevariables.ThesevariablesarecalledPublicvariables.

17.HadthesevariablesbeendeclaredinthesubproceduremyLastCellthenitwouldhavebeencalledaPrivatevariable,meaningthatthesevariableswillnotbeaccessibletoother

subprocedures.

18.AftertheProcedureiscompleted,theprogramautomaticallyreturnsbacktothepointwhereitwascalledfrom.InourcaseitgoestothenextexecutablelineafterthecallmyLastCellstatementafterline78

19.Atline83,variablerecnostoresthevaluestoredinmyLastRowincrementedby1;ThisisthenextemptyrowinourExcelfiletowritethenewlineofcustomerinformation.

Inthatrowwewritethevaluesinrecno,FileID,FirstName,andLastNameinCells1,2,3and4respectively(Lines43-45)

20.Atline61weoverwritethetextboxcontainingthephonenumberwithnothing.Inotherwordsweclearedtheslatepromptingtheusertotypeinanewphonenumber.

21.Ifamatchwasfoundthenthe‘matchFound’valueissetto1thereforelines68-88isnotexecuted.The‘If’statementbeganatline68andendedatline88withtheEndifstatement.

22.Line89closesthefilecalledChannel#123.Line90endsthesubprocedure.24.TheentireprocesswillrepeatafteryouenteranewnumberandclickontheStartcommandbutton.

25.IfatelephonenumbermatchwasnotfoundinourdatabasethenthevaluematchFoundisstill0,thentheprogramsatisfiestheconditioninline68andthereforeexecutesstatementslines68-88

Itthenblanksoutallourtextboxes(Lines70-73)byputtingnothingbetweenthequotes“”.Theprogrampopsoutamessageboxstating“NewCustomer!”inline74.

Atline78thescriptfindsthelastfilledrowoftheexcelfile.Atline83weincrementthevalueinmyLastRowby1andstoreitinvariablerecno.Thisistherownumberwhichisthenextemptyrowinourexcelfile.Lines84-87updatesthecolumns1,2,3and4withthevalueinrecno,anothing(“”),thewordNewandCustomerrespectively.

Atline89thescriptclosesChannel#1whichwasopenedtoreadtheinputfileinLine28Ifafilethathasbeenopenedforreadinghasnotbeenclosedbytheendoftheprogram,thenthatfileissaidtobelockedandisnotavailabletoanyotherprogramorpeopletoreadit.ThisisdonebytheClosestatementfollowedbythechannelnumberwithinparenthesis.Inourcaseweopenedthefileas#1inline28,sowehadtoclosethefilewhenwecompletedreadingitbytheClose(1)statement.

26.IfyouclickontheExitbuttonontheform,theprogramexecutestheprocedurecalledSubcmdExit_Click()inlines6-8.TheprogramseestheEndstatementinline7signifyingtheendoftheapplication.Thisterminatestheapplication.

Tryenteringsomeexistingnumbersinthedatabasetoseeifitpicksuptherespectivecustomerinformation

Fore.g.try9177819132or2128792135.TheapplicationshouldpickupTaviaBullockor

TanneishaBryant’sinformation.Nexttryenteringyourtelephonenumber.ItshouldsayNewCustomer!

Congratulationsonceagain!Youarenowanewmemberintheprogrammingworld.

Thisisnotacompletebookthatteachesyouvisualbasicinitsentirety.Itintroducesyoutoprogrammingbasicsandhelpsyoudispelthefearofprogramming,preparingyouforfurtherselfhelpreading.7MOREREADING

Ifyouhavefollowedthisbookcompletelythenyouwillhavesufficientknowledgeandbackgroundtopick-upfromwherethisbookends.

Thereareseveralfreeonlineresourcesandbooksinthemarketwhichyoucanuseforadvancedreading.Mostimportantofall,youwillbeabletodobasicmanipulationsofdataresidingeitherinanexternaltextfileorinanExcelfile.

Youhavealreadyfinishedtakingyourfirststepinthefieldofsoftwareprogramming.Suggestionsforimprovingthisbookiswelcome,pleaseemailmeatauthor.ProgrammingBasics@gmail.comThankyouforbeingwithmeinthisjourney!ABOUTTHEAUTHORMohanPalletihasaPostGraduateDegreeinComputerScienceEngineeringandaGraduateDegreeinElectronicsandCommunicationEngineering.HehasextensiveexperienceinthefieldofGeospatialScienceandResearch.

MohanisacovetedmotivationalspeakerforinternationalconferencesconcerningtheadvancementofyouthintheInformationalTechnologymarketplace.

Hisstyleisthatofamotivator.Heencouragesouryouthtoexaminetheworldofprogrammingtoassisttheminlaunchingbotheducationalaswellascareergoals.Healsopreparesinstructorstobeabletostartanintroductorycourseinsoftwareprogramming.

HeisgiftedinmakingcomplicatedsubjectslikeMath,ScienceandSoftwareProgramminglookeasyandheisabletodrawtheaudienceintothesefieldsanddispeltheirfears.

MohanPallettiisavailableforspeakingengagements,schedulepermitting.