VBA Ranges - Getting and Setting Cell Values

To get a cell's value in VBA, we need to refer to it with the Range object and then call the .Value property. We'll use the following ... Inthepreviouspost,weintroducedthe VBARangeobject. ThisgaveusthefoundationofworkingwithRangesinVBA.Intoday’spost,I’d liketodiscusshowtogetandsetcellvaluesthroughVBA.Thiswillcontinue tobuildupourunderstandingoftheVBARangeobjectandhowtouseit.There areseveralwaysyoucangetandsetcellvalueswithVBAandI’lldomybestto coverallthenecessities,butatthesametimekeepingitshortandtothe point.Let’sgetstarted. GettingCellValues Togetacell’svalueinVBA,weneedtorefertoitwiththeRangeobjectand thencallthe.Valueproperty. We’llusethefollowingspreadsheetforourexample.It’sasimpletablewith somenamesinit. TogetthevaluefromcellA2youcanusethiscodesnippet: OptionExplicit PublicSubGetCellValue() DimvalAsString val=Range("A2").Value Debug.Printval EndSub ThiswilltakecellA2andputitinthevariableval.Thenweprintoutthe valueintheImmediateWindow(whichthevalueinourexampleisJoseph). Youcanalsosettherangetoavariableandaccessthevaluefromthatvariable aswell: OptionExplicit PublicSubGetCellValue() DimcellAsRange Setcell=Range("A2") Debug.Printcell.Value EndSub Whathappensifyouuse.Valueonasetofcells? Let’schangeourpreviouscodesnippettothefollowing: OptionExplicit PublicSubGetCellValue() DimcellAsRange Setcell=Range("A2:A5") Debug.Printcell.Value'willthrowanerror"TypeMismatch" EndSub Ifyourunthiscode,youwillgetanerrorstatingthatthereisatype mismatch. What’sgoingonhere? Theproblemisthatwhenyouworkwithasetofcells,.Valuecanonlyreturn asinglevalue.SowhenweaskVBAtoreturn.Valueonourvariable(which referstomultiplecells),the.Valuepropertydoesn’tknowwhichcellweare referringto. Howdoyougetasinglecellfromasetofcells? Inordertouse.Valuetogetavaluefromacell,weneedtorefertoa singlecellfromtherangeofcellsinourvariable.Thewaywedothatiswith theCells()VBAfunction. TheRange.CellsFunction TheCells()functionisawaytotakearangeofcellsandreturnasingle cellfromtheset.Hereisthefunctiondefined: Cells(row_number,column_number) Parameter Type Definition row_number Integer Therownumberfromwithintherangethatyouwanttoreferto. column_number Integer Thecolumnnumberfromwithintherangethatyouwanttoreferto. Takealookatthefollowingcode: OptionExplicit PublicSubGetCellValue() DimcellRangeAsRange SetcellRange=Range("A2:A5") Debug.PrintcellRange.cells(1,1).Value EndSub HerewetooktherangeofA2:A5andreferredtorow1column1.Sincethe rangevariablecellRangereferstoA2:A5,thefirstrowisrow2andthe firstcolumnisA. BECAREFUL! WhenusingtheCells()function,rememberthatrow1andcolumn1represent thetop-leftmostcellwithintherangethattheCells()functionisworking on.IfyourrangeisA1:D5,thenCells(1,1)willrefertoA1,butifyour rangeisB2:D6,thenCells(1,1)referstoB2. Ok,thatcoversgettingcellvaluesfromrangeobjects,nowlet’sdiscuss settingcellvalueswithrangeobjects. Doesthisarticlehelpyou?Ifso,pleaseconsidersupportingmewithacoffee☕️ SettingCellValues Inordertosetacell’svalue,youcanusethesame.Valuepropertywhen referringtoacell.Inthisexample,we’lltakeA2’svalueandchangeitfrom JosephtoJohn: OptionExplicit PublicSubSetCellValue() DimcellRangeAsRange SetcellRange=Range("A2") cellRange.Value="John" Debug.PrintcellRange.Value EndSub FirstwesetthevariablecellRangetoA2.Thenwesaid cellRange.Value="John"whichchangesthevariable’s.Valueproperty. Remember,though,thatthevariableisareferencetocellA2,sowhateveryou dotothatvariable,youalsodotocellA2intheworksheet.Finally,we outputthevalueofA2intotheImmediateWindowtoseethatitchanged. Wecanalsoseethevaluechangedintheworksheetafterwerunthiscode: Howdoyousetmultiplecells’values? RememberhowIsaidthatyoucanonlyreadfromonecellusing.Value?Well, whensettingvalues,youcanactuallysetmultiplecellsatonetimebyusing .Value.Takealookatthefollowingcode: OptionExplicit PublicSubSetCellValue() DimcellRangeAsRange SetcellRange=Range("A2:A5") cellRange.Value="John"'WillsetALLvaluesintherangeto"John" EndSub Ifyouranthiscode,itwouldsetallA2:A5’scellstoJohn: …whoops. Well,maybeyou’dactuallywanttodothisforsomeotherscenarios,likewhen youwantabunchofcellstorepeatavalue. Let’stakearealexampleforasecond.Let’ssaywehavetwocolumns, FirstNameandLastName.WewanttotaketheLastNamecolumnandplace itsvalueaftertheFirstName’svalue;essentiallycombiningthevaluesto makeasingleNamecolumn. Here’soursampledata: Ourtaskistocombinethefirstandlastnamecolumnsandplacetheresultin columnA.Howdowedothat? OnesolutionistoloopthroughcellsA2throughA5andthensetthatcell’s valuetoitsownvalue,plusaspace,plusthelastnameofthecellrightnext toit. Soundseasyenough,let’scodeitup: OptionExplicit PublicSubSetCellValues() DimnamesAsRange Setnames=Range("A2:A5") DimcellAsRange ForEachcellInnames cell.Value=cell.Value&""&cell.Offset(0,1).Value Nextcell EndSub Let’sstepthroughthecode. First,wecreateavariablecallednames.Then,wesetthattorange A2:A5. Next,wecreateavariablecalledcell.Thisisgoingtobeatemporary variablethatwillchangewitheachiterationoftheloop. Then,wecreatetheloop.Here,we’reloopingthroughthenamesrangeobject andsettingthecurrentitemtothecellvariable.Thismeansthateachtime werunthroughtheloop,cellrepresentsasinglerangeobject. *Thefirsttimetheloopisrun,cellissettoA2.Then,A3,next A4,andfinallyA5.Afterthat,therearenomorecellstogothroughin thenamesvariable,sotheloopends. I’llgooverhowtoloopthroughrangesinafuturepostsincethispostis alreadylongenough! Nowwe’rereadytocombinethefirstandlastnames.Howwedothatiswith anotherRangefunctioncalledOffset(_rows_,_columns_).Theideawiththis functionisthatifyou’reonacelllikeA2andyousaycell.Offset(0,1) whatwe’rereallysayingis“moveoveronecolumntotheright”.Thisputsus oncellB2.That’showwe’reabletogetthelastnameinourexample. I’lldiscusshowtousetheOffset()functioninmoredetailinafuture post.Again,thisposthasgoneonlongenough. Herearetheresultsofthecodeafterwerunit: Fromhere,wecouldchangetheA1celltojustNameanddeletecolumnB altogether. GettingandSettingCellValuesfromaNamedRangeorTableName OnelastthingI’dliketotouchoniswhenyouusetheRange()function,you canuseanamedrangeortablenameinsteadofarangelikeA2:A5.Inour firstexample,ourdataisinatablenamedTable1.Torefertothedataof thetable,wecouldusethefollowing: OptionExplicit PublicSubGetCellValue() DimcellRangeAsRange SetcellRange=Range("Table1") Debug.PrintcellRange.cells(1,1).Value EndSub Andtorefertotheentiretable,wecanleverage structuredreferences like so: OptionExplicit PublicSubGetCellValue() DimcellRangeAsRange SetcellRange=Range("Table1[#All]") Debug.PrintcellRange.cells(1,1).Value EndSub ThiswillreturnA1’svalue“Name”sincethetablestartsinA1. Also,ifyou’renewtoExcelTables, clickheretolearnmore. What’snext? Honestly,thereissomuchtodiscusswithrangeobjectsinVBA.I’llbe touchingonmanymoretopicsregardingrangesinVBAinupcomingpostssuchas: Modifyingcellcolors Findingcellsbytheirtextvalues Filteringdata Gettingthelastrowinarange(youneedthismoreoftenthanyouthink) I’llcomebacktothispostandputlinkstothesepostsasIcreatethem. Ifyouenjoyedthiscontent,pleaseshareand subscribe!←IntrototheVBARangeObjectLARGE()FunctioninExcel→