INTRO TO COMPUTING Lecture Notes! Class 10& 11,
INTRO TO COMPUTING Lecture Notes! Class 10& 11, 1300
Popular in Introduction to Engineering Computing
Popular in Applied Science
One Day of Notes
verified elite notetaker
Molecular, Cellular And Developmental Biology
verified elite notetaker
One Day of Notes
verified elite notetaker
One Day of Notes
verified elite notetaker
One Day of Notes
verified elite notetaker
This 17 page Class Notes was uploaded by Dana Stamo on Saturday October 4, 2014. The Class Notes belongs to 1300 at University of Colorado taught by a professor in Fall. Since its upload, it has received 239 views. For similar materials see Introduction to Engineering Computing in Applied Science at University of Colorado.
Reviews for INTRO TO COMPUTING Lecture Notes! Class 10& 11,
Report this Material
What is Karma?
Karma is the currency of StudySoup.
You can buy or earn more Karma at anytime and redeem it for class notes, study guides, flashcards, and more!
Date Created: 10/04/14
COMPUTING VBA BASICS Sept 30 These notes reference the powerpoint presentation for Class lO on D2L use VBA to automate repetitive activities in Excel expand Excel39s capabilities access other software programs from Excel convenient input and output for Excel programs while VBA and Excel each have their own functions we can perform a dark ritual er I mean create a code that can invoke functions from Excel that don39t exist in VBA or we can use functions that exist in VBA and not Excel to work with our spreadsheet Visual Basic Editor used to modify VBA environment Accessing VBE if you lack a Developer tab ExcelgtgtOptionsgtgtCustomize Ribbongtgt and make sure Developer is checked you can either open VBE from the Developer tab or by hitting the keyboard shortcut Alt Ell fn Alt Ell on a mac this shortcut works both ways it39s basically a toggle between Excel and VBE n Home J Layout i Tables J Cllarts J Smarmrt J Formulas i Data J Review 0D A I l I H Visual Basic Add Ins 5 Form Coi g g P0 i Button 3 Radio Button scmnna Tm acros Record Relative Reference Idd ns P iii Chet 3 U 5 i immhn B S k p while we39re here messing around with keyboard shortcuts and whatnot you might as well know that Alt F8 fn Alt F8 on a mac opens up your Macro Dialogue Box which is basically the codes you39ve written in VBA it opens up a list of them you can run from Excel the characteristics of VBE are a little different between mac and windows but both include the Project Explorer for sifting through the various codes you39ve written the Code Window for typing up your codes and the Properties Window for changing properties I imagine but we39ve not had the most detailed run down of the purposes of this aspect of VBE l Iiuusofl39isualBasirBuulampl 7 a 0 V E H y 3 I g g ij 5 J ifne gun yum men rgmm Qebug gun Ioals gaains macaw elp iv iia 39 ii B im ii ii L Genera 39 AddFls 39 I I an Option Explicit I i i Ii L Project Explorer p I suwdmo Editor g vmpmjmmorkhookl Dim x As Double y As Double 2 As Double x ActiveCelValue i atpvbaemcls ATPVBAEMXLAMI 39 k l y lnputBoxquotFnter y quot 71 Q nnnnnn jcrl Don Microsoft Excel Object 4 5 VBAProet runcnssuxii E Sheetl sheen Y a fi 5 V8APro1e4tPElSONALXl5 Thisworkbook Acmecece5339 3 Z 3 g VBAPvoject slgnaplotxln H g VaAProiet splemovexla Hii ES I Modulel and sub Properties VBAPruject E VBAProje1t Project I Ahhabetir caueoovIaed IE VBAProiect Properties Window Code Window iii A the mac VBE is a little awkward in that it doesn39t open all your windows at once the way windows VBE does You have to go in and manually pull them all up from the View menu Opening VBE from a mac only pulls up the Project Explorer window to open the Properties tab go to Viewgtgt Properties Window or just hit fn F6 Creating VBA Code l Use the Macro Recorder 2 Write code yourself from scratch Macro Recorder records all the buttons you hit and compiles a code to repeat all the buttons with a keyboard shortcut you can get a lot of unnecessary code with this method so make sure you know how to sift through and delete what39s not needed So suppose you39re working on a code and you find that you39re creating a lot of tables with some pretty long labels and every time you create a new table with that oh so long label you need to go and format the cell so the text is centered both horizontally and vertically and wrapped around the cell Oh if only there was a way to write a code with a keyboard shortcut that would do all that automatically Then you remember there is a way It39s called VBA And you39re learning about that right now Damn that39s convenient first select your cell with that really long label in your spreadsheet then click on the Developer tab and click Record Macro E 1 C ecoroi I 1 Jquote Ftl3T Fi i ufrE39j 78 wuualfiamc h I I E3 A i339 Se ii e p0k quotcquot d i Editor Macros a Record H II elative Reference 5 Windows Mac a new window will pop up asking what you39d like to name the code this is up to you I39m going to name this one ForConveniencePurposes Try to pick a name that39s concise and descriptive And fun if you39re like me Then you can throw in a keyboard shortcut which again is up to you Let39s do OptionCmdC This is a little different on Windows your shortcuts are usually defaulted to Ctrl Shift lta letter of your choicegt Store your macro somewhere I chose my Personal Macro Workbook but right now for our porpoises it doesn39t really matter Keep it in mind for later when you want to organize your codes Finally put in a description of what your code does this is not mandatory you can just leave it blank Record Macro Macro name ForConveniencePu rposes Shortcut key Store macro in OptionCmd C Personal Macro Workbook Description center horizontal and vertical wrap texd Cancel OK now we can actually record our code Once you39re ready make sure you already selected your active cell on the spreadsheet before you click Record Macro start punching in everything you want done to your cell that is center vertical and horizontal and wrap text I trust you know how to do this If not google is a thing that exists Once you finish inputting all your details hit Stop Recording A Home J Layout y Tables 1 Ch quotIn elative Reference 3 9 be Editor Macros Record B2 fr rail 4 A C rabbits are cooland fun Skateboard 5 Tu now you can tesf our your brand new code Write another long label in a cell and try the keyboard shortcut It should do the same thing If your code doesn39t work you probably didn39t record properly Try again Check out your brand new rockin code by hitting the keyboard shortcut for VBE and selecting your project from the Project Explorer You39re going to get a terrifying wall of code get a blanket and some hot chocolate and we39ll sift through the big scary code 8 O 6 Personal Macro Workbook Modulel Code IO Www v mmmmme vi now code isn39t too difficult to interpret reverse Sub ForConveniencePurposes 39 engineer so remember what we did with our code and 39 ForConveniencePurposes Macro I I centerhorizontaandverticawraptext delete everything P lsn t IW W first let39s get rid of everything before the very Wwmmmm last step because every With Selection part of this VerticaAlignment xBottom Wwwwhm code is one step in our process every time we hit a OrIentatIon O ife new button in Excel it generated this much code we EEQETQS could take care of it all at the bottom with the d i h wm final step HorizontalAIgnment xlCenter I I I E mmm additionally we can delete anything that39s false i e or 0 because they don39t make any change given that ShrinkToFit False hmgamame they39re false or 0 So if they aren39t there at all n It Wm gghwmm mmr nothing will happen You can see below everything you j E m39 need to take out 0 O A lmu Comment are written in green and introduced with ShrinkToFit False mm am me an They have absolutely no effect on the actual W gWmmmmH code This is where VBA enters your comment section ETmm and a couple other tidbits of information 0 39 39 O AlIri1tltteln0tn False ShrinkToFit False D H I F39enurul MILro Workbook Mudult1 tCurr hnmm Hucmmnnhnn mnmnas 33 EiF5K39oI1venIenceFurposes0 39 pO quot FmtionwniencrPurprses Macro 39 center horizontal and vertical wrap tent4 39 Keyboard Shortcut DptionCrndShiftC I Saw In 39 lhlignmcnt xlleft eni Align xl all 51 Tax tion 0 p M F Inde el 39 Sl1ri t alse atII False En th ion H mm l mu xlcemc e lignme xlio 3quot pTrxtF e 0I itIquotlliliO 0 Juddlnd 1 se Sl1ri o 39 F 5e 239 II alse En 39 Sal Ho natal 39 t a tuner nlcal t nler fiy39Xt I nutquot I II 3939I IF E M Sl1 oFit e 39 1Cells alsfV EH l Wllh action HoriontalAIignm nt xlctnur Vcrtlcalhlbgnment xl emer Wra Text True it is advised that you run your code after deleting all this and make sure it still works You can use the Macro Recorder to l Create a button in the toolbar to run a code 2 Create a button on the spreadsheet to run a code yeah I know it39s pretty wild 3 Move the Macro module to let it run in other workbooks or all of them It s a bit easier to read code than write it from scratch so use the Recorder whenever you can do something only with Excel functions Objectoriented Programming objects are the big ones the programs the entities that do things within Excel the big object there are sub objects smaller entities that do special things any object has properties basically attributes that we can mess around with as the omniscient programmers here is your mighty programming scepter to peruse these objects classes and properties open up the Object Bowser I mean Browser In Windows just hit the button in the toolbar that looks like a box with a couple shapes On a Mac go to ViewgtgtObject Bowser or hit Ctrl Comm B 8 3 6 Object Browser ltAlI Librariesgt v 139 Classes Members of 39ltgobasgt39 i O ltgobasgt 9 Abs NE NE AboveAverage ActiveCel 0V Addln ActiveChart Rf Addlns ActivePrinter J Adjustments ActiveSheet 1 Application ActiveWindow 3 Areas Activeworkbook Q Assistant Addlns AutoCorrect Q AddPs Hf AutoFiter 5 AppActivate ltA Librariesgt In the left column you have all your object classes sub objects of his majesty VBA the omnipotent object in the right column you have the properties of the object classes this is important for when you start referencing things in VBA that are in Excel The complete references go through everything the Range is inside of but that39s not necessary It39s like if your friend wanted to go see Becca and you told her Becca39s house was in the universe in the Milky Way galaxy on planet Earth in North America in Colorado in Boulder on algoiaerknkasakhsgk it s too much You can just say it39s a couple houses down the street Same with Excel and VBA Instead of typing ApplicationWorkbooks Project6 Worksheets Main Range Bl6 you can just type Range Bl6 and Excel will understand completely if it s unambiguous I mean if you told an alien that Becca39s house was two houses down the street they d be very confused Properties are attributes of object Range Bl3 Width Methods are actions taken on properties Range Bl3 Clear Events are happenings that objects respond to Open Range Range is an object that gives the code something to work with the properties of range include Value default if nothing is written uses whatever is in the active cell Text string of letters in the cell Count the number of cells in a selection Column Row indexed number of columns or rows in a selection Address absolute address of a cell or range the methods of range include Select Copy Paste Delete we can use Range to hone in on a cell in many different ways Range D4 you need quotations marks because it makes it clear to VBA that D4 is terminology you39re referencing from Excel It39s like if you need to reference Excel in VBA just pretend you39re being really sarcastic and pretentious because you know a software that VBA can39t communicate with directly Range AlB5 Range Pressure suppose you named a range Pressure this would select that range Range DD selects all of column D Range 33 selects all of row 3 Suppose you39re really good friends with VBA and you speak his language so you don39t want to be sarcastic and pretentious when talking to VBA then you can reference cells with the Cell functions Cells45 so now you no longer need quotation marks but you must refer to cells with only numbers 45 refers to row 4 column 5 Offset can shift your cell selection over Range Temp Offset moves the range over and down l cell Then when you become a bamf and can write these ranges really fast you can use them in conjunction with eachother like SelectionCells44 which selects cells 4 columns over and 4 rows down from the top left corner of a selection of cells EXAMPLES How would you refer to cell C4 Range C4 Cells34 A selection Selection l row up and l column right of the active cell ActiveCellOffset ll 3m39row 3m39column of a selection Selection33 Name a range Sub Names the name of your code Dim CellRange as Range we39ll talk about dim soon Set CellRange Range AlB3 define the range CellRangeName Temperature give it a name with the Name function End Sub Our operators in VBA are generally the same as in Excel I39ll just note the differences integer division notice the slash goes the other way mod remainder amp connects strings of code ltgt not equal to Logical Operators Not And Or XOr Eqv there39s quite a list of functions in VBA lmp I39ll just list the ones we39ll primarily be using right now Sqr sqrt Log natural log ln MsgBox displays the results in a popup window InputBox asks the user to input information in a popup window Create a VBA sub that will replace the value in the active cell it39s negative value first put some value in a cell I put a 9 in cell C3 open up VBA with your shortcut Alt Fll or fn Alt Fll on a mac click on InsertgtgtModule to open up a new blank sheet for your code all our codes will start with typing Option Explicit which basically requires you to define all your variables but we39ll get into that in the next set of notes same with dimming Dim x as double all will be explained accept it as the truth until you know better give your code a snazzy title above the dim followed by an empty set of for right now Then we set y as our ActiveCellValue so VBA takes whatever will be in our selected cell which we select prior to running the code and make it equal to y We make x y to negate the value of y Then we set y equal to our active cell Your code should look like this 6 6 6 General V just blindly introduced with Sub and Workbook2 MOdL Mal Op onExpHdt Sub MakeMeNegative Dim y As Double x As Double y ActiveCeVaue X Y ActiveCeVaue x land Sub you can run this code either by pressing the little play button at the top of VBE or running it from the Macro window in Excel Alt F8 or fn Alt F8 on mac here39s an easier solution that is a bit more complex for the weeaboo coder I Option Explicit Sub MakeMeNegative Ac veCeH Ac veCeH End Sub Create a VBA sub that will ask the user for a number in a InputBox then display the square of this number in a MsgBox as codes get more difficult you39ll want to start making flowcharts but we39ll start making them now so you start to get the hang of it There are set rules about flowcharts because they39re universal among START and END are circles or rounded squares actions that and operations are squares programmers interact with the user are slanty squares sea D J T Option Explicit rgr Sub SquareMeBaby 2 Dim x As Double y As Double x InputBoxquotPease enter an x value quot yxA2 MsgBox quotYour y value is quot amp y 32 End Sub 445 EMX Your y value is 16 l 0I we don39t need to get very into this code and how it works just yet just admire the pretty code say ooh ah a few times and you39ll be set for right now now we let x the input box which pulls up a little window that says whatever we put in the quotations and then it has a nice little box for the user to throw in a number I put 4 So then we run x through a square and set that value equal to y Also make sure there are spaces between your x A and 2 or else the code won39t work Finally we make a message box pop up that tells us whatever sentence we put in quotation marks with a amp sign that connects the text to our y value Since the y is not in the quotation marks the code understands that y is the actual y value and not just the letter y Behold the power of the quotes COMPUTING INTRO TO VBA Oct 2 these notes reference the powerpoint presentation for Class ll on D2L Data Types Dim declaring dimensionalizing your variables so the program knows what to do with them Always type Option Explicit at the top of all your code requires declaring of variables which makes it easier to troubleshoot later and it39s a good coding habit You can set Excel to do this automatically for you whenever you insert a new module there is a tutorial for this on the slideshow for Class ll on slide 8 You don39t need to define your variables writing Dim x is sufficient and VBA will detect what data type you39re using But that39s bad coding and you are not in a computing class to learn bad coding Most Common Data Types 1 Double decimal fractions and possibly exponents where high precision or extended range is required most common most variables in this course will be double Precise to l5 or l6 decimal places which is seriously overkill I can39t imagine why you39d need so many decimal places Number ranges from lO 308 down to lO 309 that39s a really bigsmall number 8 bytes of memory not too important now that computers can process large amounts of memory but you know fun fact this is the data type Excel uses to store cell information on the spreadsheet 2 Integer counting numbers as precise as an integer can get Ranges from 32768 to 32767 two bytes of memory 3 String used to store text Rabbit surrounded by quotations 9 code interprets it as a string each character uses one byte characters stored according to standard ASCII code I wish I knew what this means but I m sure it39s not prime test material Additional Data Types 1 Single used for most numbers with decimal fractions and possibly exponents II Precision about 67 decimal points Range from lO 38 to lO 39 uses 4 bytes of memory used to be the most common data type 2 Long integers where extended range is required really really big numbers Range 2 billion to 2 billion 3 Boolean used for TF information two constant values TRUE or FALSE 2 bytes are used for each Boolean quantity ghosts favorite type of code 4 Variant adjusts automatically to the data type required chameleon data type storage requirements vary VBA detects what X is if you don39t dim Dim x default is Variant 5 Range used to store a range of cells also data types for currency and date user defined data types variables identify storage locations like addresses on the sides of mailboxes SCOPE of a Variable how far the variable reaches essentially keeps within its own code l Local Variable within a single procedure Sub or Function declared using Dim or Static statement 2 Module Variable within current module declared using Dim statement before Sub or Function statement 3 Public Variable reaches everywhere in all modules open declared using Public statement at module level Module 7 pl w 32 quot39 iLE F Wodufef lq4biM 34 fQ j SH 39JT jquot1 quotr39I F rjquot 39 39 39 e39 I43 C C 6 Workbookl Modulel Code 9 8 6 Workbookl Modue2 Code General f Dolt General i Howdy option Expncit gt Option Explicit Dim k AS Integer 5 Public RAs Single oneo iquotx ing Equotd5 b EndSub Function Dotx Function Howdy Dim y As Double End Function End Function was declared in Module 1 so it can only be used in Module 1 is a local variable in Function Dolt can39t be seen anywhere else is anywhere in project by the Public argument in Sub Two is an argument formal parameter dummy variable is a local variable in sub two and can39t be seen anywhere else Static alternate to Dim use static instead of dim when you want the variable to retain its value after the procedure is done Symbolic Constants you can name some variables so they have the same value all the time CilltU39ltIW Const keyword Const Rgas As Single 83l43 Naming Rules for Subs and Functions must start with a letter number some punctuation cannot look like a cell reference cannot use periods or spaces VBA is not case sensitive Subroutines and Functions Subs or Macros executed by running it from Excel VBE or called from another code created with Macro recorder or typed from scratch may have arguments that require input Sub name optional arguments code End Sub Input Options InputBox ActiveCell chosen before Sub is run Selection chosen before Sub is run A fixed Range Output Options MsgBox displayed to user ActiveCell chosen before Sub is run Selection chosen before Sub is run A fixed Range Functions executed when the VBA subprogram name is referenced return values in place of their names can only be typed may also have several input arguments Function name optional arguments code End Function Example Problem I39m going to try to figure out a good way to do this So far I39m taking pictures of my code as I go in class then I compile it all here for you with little pointers and tips mixed in these wouldn39t be good notes without pointers I know one of the most annoying things about this computing class is I can39t keep track of my processes because we often delete and rewrite the code VBA sub that would take input from user InputBox and a display square root in a message box MsgBox b display square root in the active cell ActiveCell c display square root in cell C3 Cells33 or Range C3 it is strongly advised to create a flowchart for all your code it39ll be especially helpful when you get into more complicated code even though right now it seems as simple and useful as a flowchart for how to put on pants You will also need to know how to draw these for future quizzes and the final exam Know that START and END are always either circles or squares with rounded edges the functions that interact with the user are parallelograms or rhombuses I forget what those are called I39ll call them slanty squares Squares that look like someone gave them a good shove And functions that are input in the code are just plain squares squares that have not been bumped or their corners sanded down into nubs A lot of this explanation was to fill up this empty space at the bottom of this page Skateboards quotquot 39H 39 7 FE If 7 3 r quot 39gt1I iquot lr 1 I J1 17L I L I a39 J394 lE 39 L I J J 391 1 I A la V gr i J4 rill t1939ii L quot T J C 014 R rquot E 1 I y tr 7 T 39 1i cw g ttt uf v39rquot 5 1 K J 39 I39ll 39 IF E J xi IPFEJ Li H 1 ll rj l39quot7 gj 7 quoti J quot V l139 iT1 39 15 i 4 if Ii 3 gran kh J C C 8 Workbook2 Modulel Code iGenera i TakeSquareRoot Option Explicit Sub TakeSquareRoot Dim x As Double y As Double x nputBoxquotEnter x quot Whatever is on the right side of sign is what will be assigned the x value v Sqrx MsgBox y Endsub We name our Sub TakeSquareRoot and put empty parentheses because we do not need to input any other arguments but the parentheses still need to be there even if they are empty a shell of an argument declare your dims X and y Create an input box for the user to interact with the code y VBA function of square root sqr display y in a MsgBox The result should look something like this I General TakeSquareRoot i Option Explicit I Sub TakeSquareRoot0 Dim x As Double y As Double x lnputBoxquotEnter x quot Whatever is on the right side of sign is what will be assigned the x Value v Sqrx MsgBox quotThe square root of your number is quot amp FormatNumbery 2 amp Have a lovely day You39re awesomequot 39 FormatNumbery2 will give two places to the right of the decimal 39 All string must be in the same set of parentheses in the MsgBox End Sub The square root of your number is 265 Have a lovely day You39re awesome Zl il J1 II to make our MsgBox say more than just the y value we use amp to add extra formatting all of the string must be in the same set of parentheses but we only put quotation marks around what we want stated FormatNumbery2 makes sure that when the y value is displayed it only shows 2 numbers to the right of the decimal not significant figures that39s a totally different confusing exotic concept created by chemistry professors to take points off our labs and Excel is too cool to comprehend that silliness C 3 3 Workbook2 Modulel Code Genera t TakeSquareRoot Option Explicit Sub TakeSquareRoot Dim x As Double y As Double x nputBoxquotEnter x quot Whatever is on the right side of sign is what will be assigned the x value v Sqrx ActiveCeVaue y value is optional because it is default you can just type ActiveCel y IIII B 39F we modify our previous code to display the result in the active cell selected before running the code instead of presenting a MsgBox we write ActiveCellValuey the Value is default it does not need to be typed but once again we are not learning how to write slacky code in this code notice that the y value is not formatted to 2 decimal places so it will give back the l5 or l6 decimal places for our y value The result should look something like this 9 g Button Radio Button Scroll Bar Tex1 Macros Record Relative Reference 5 AddIns E quotl Check B x i List Box 39 quotC mb 3 Iii spin B5 C C C 6 Workbookl Modulel Code A C c General i TakeSquareRoot 0p onExpHdt Sub39TakeSquareRoot0 Dim x As Double y As Double x InputBoxquotEnter x quot Y Sqrx ActiveCelValue yl End Sub C C 8 Workbook2 Modulel Code General i TakeSquareRoot Option Explicit Sub TakeSquareRoot Dim x As Double y As Double x nputBoxquotEnter x quot Whatever is on the right side of sign is what will be assigned the x value v Sqrx Selection FormatNumbery 2 End Sub C we modify our code so that the answer is displayed in a selection of cells selected prior to running the code this code formats y to 2 decimal places with FormatNumbery2 The result should look something like this J V 1391 Check Box l iii List Box I vl Combo Box w lCl39OS Record Relative Reference l AddIns 8 0 3 Workboo fxl 265 I I I I JgmI General 0p onExpHcn 3 2 i2 Sub TakeSquareRoot 265 265 165 Dirnlx As BDouquotbEe y As quotDouble L65 165 165 quot xquotquot quotput x quotterx39 v Sqrx Selection FormatNumbery 2b End Sub we can also modify our code to display the result in a specific cell like C3 isn39t that what we were supposed to do Whoops Here it is IcIcuIvc lClClCIlLC ruu39quot3 866 wlt 2 GeneraD C I Option Explicit 254575131 Sub TakeSquareRoot Dim x As Double y As Double x nputBoxquotEnter x quot v Sqrx RangequotC3quot y End Sub We can either write the desired cell as Range C3 y or Cells33 y They do the same thing So many options in coding If you want to proof and debug your code you can use the step feature which takes you through the code line by line and shows you what39s going on This way you can catch errors when something you code goes wonky in action That ll be a new term in my notes by the way wonky in action WIA On Macs you hit CommandShiftI Or just go in the menu to DebuggtgtStep Into But the keyboard shortcut is more efficient than going through that menu for every step Suppose we wanted to change the value input from user interactive InputBox to dumping all the work on the code and asking it to take the value out of cell Al I Visual Basic Add lns C k 27 l Button l 0 Radio Ruttnn 9 a 0 Q T T 393939 f Editor Macros Record Relatiire Refei cenerag D5 A 3 c 7 23965 Sub TakeSquareRoot Dim x As Double y As Double x RangequotA1quot v Sqrx RangequotB1quot FormatNumbery 2 End Sub Option Explicit L l ll hLUNlI l ll LDWVQUI change the InputBox line to set X Range Al the ranges work the same way here as they did in part C with displaying results I also let the result be displayed in cell Bl right next to cell Al where the data was input the result y is formatted to 2 decimal places Using the format X Cellsll would also work I39m going to do something that will really make you appreciate your purchase of these notes now EXAMPLES That weren39t covered in class Let39s see how this goes WARNING Use my work on these examples at your own risk as I did not complete these with Charlie39s help I don39t know if they are what he39s looking for or written with the proper solution But being that they are code I can check if they39re working the way I want them to so you have my guarantee that I will never post a code that I have not checked but I cannot guarantee it will get you full points on a quiz or homework Create a VBA sub that will square cell 22 of a current selection and place it in cell Al 99 I Hquot r GeneraI i SquareThisPs I 0 I Qq 2 39 39 Option Explicit Editor Macros Record Relative Reference Add ns g i D4 fx 9 Sub SquareThisPls I II A I oo I C I L I 5 Dim x As Double y As Double 1 25 x SelectionCels2 2 2 1 2 3 y x A 2 k 4 5 6 39 there must be spaces between your x A and 2 in order for this to work Not all VBE do it automatical 7 8 9 39 So keep your wits about you RangequotA1quot y L End Sub 8 9 the trick to this code is making the code find cell 22 of a selection periods work kind of like parentheses in Excel codes when you would write equations like MMULTMlNVERSEAB and you39d have a function within a function Our outside function first draws the code s attention to our selection we make prior to running the code the period introduces the next part of our code which calls attention to the cell 22 but since this function is within the Selection code VBA will look for the cell 22 within our selection then we politely demand that the result be displayed in Range Al y Write a VBA sub that will count the number of cells in a selection and display the result in a MsgBox I 1 General Coun I I Option Explicit 2 3 2 3 Sub CountPls Dim y As Integer y WorksheetFunctionCountSelection MsgBox quotThe number of cells in your selection is quot amp y End Sub The number of cells in your selection is 6 Dim y as an integer because I can39t imagine we39d need decimals to count cells we invoke the Excel function COUNT and let it count the selection that we choose prior to running the code then the MsgBox tells us the answer Create a sub that will display the number of rows and columns of a selection in a message box 393 U i 1 1 1 1 1 1 1 1 1 1 1 Genera i Cou ntPls 25 Option Explicit I Sub CountPls Dim k As ntegerj As Integer k SeectionRowsCount j SelectionCoumnsCount MsgBox quotThe number of rows in your selection is quot 81 k 81quot rows and the number of columns is quot ampj amp quot coumns3939 2 I I I I I I I I I I I l I I figure Rows Columns and Count are all embedded in the VBA software no need to invoke the Excel functions we dim k and j as integers because you can39t have half a cell Create a sub that asks the user for a number with an input box then adds that number to the number in the active cell and displays the answer two cells to the right and two cells down from the active cell 3 I I I 39 I 2 I1 General Option Explicit Sub AddPls Dim x As Double y As Double 2 As Double x ActiveCelVaue y lnputBoxquotEnter y quot zyx ActiveCeCes3 3 2 End Sub the weird thing about this code is that in order to display the answer two cells to the right and two cells down from the active cell we need to input that value relative to the active cell so we actually say ActiveCellCells33 so if the active cell was Al our answer would have to be in cell C3 in order to be two down and two to the right C3 translates to 33 in VBA
Are you sure you want to buy this material for
You're already Subscribed!
Looks like you've already subscribed to StudySoup, you won't need to purchase another subscription to get this material. To access this material simply click 'View Full Document'