Accessing a spreadsheet range directly from vba note

This preview shows page 3 - 7 out of 13 pages.

56Accessing a spreadsheet range directly from VBAWhat then is the result ofWhat if we want the value inthe cell below the ActiveCell?What if we want tochange the ActiveCellto the cell below thecurrent ActiveCell?Accessing a spreadsheet range directly from VBANote: It is not necessary toreference a particularworksheet since a rangename is global throughoutthe workbook.The "ActiveCell" is at the topof the "Selection" and its valuecan be referenced in VBA byNote: "Value" is the defaultproperty and can be left out
47Accessing a spreadsheet range directly from VBAMoving the ActiveCell to the end of a range of filled cells(record a macro to see how this is done)Selecting from the ActiveCell to the endof a range of filled cells(again, figure this out by recording a macro)8Accessing a spreadsheet range directly from VBASelecting an entire range of cells (Ctrl - * in Excel)Determining the size (no.of rows and columns) of aselected range of cellsThis is important in writing VBA code for an arbitraryselected range of cells.
59Cell ranges as arguments to functionsIt is common to want to write a function that makescalculations for one or more arguments that are rangesof cells.General pattern:Invoke function on spreadsheet with pH argument:Trace execution of function withbreakpoint -- check value of nRows:nRows = 1510Cell ranges as arguments to functionsExample: Find the second largest item in arange of cellsNote: We should take advantage of Excel's (not VBA's)Max function.find the maximumvalue in DataRangeto start, set the second maximumvalue = the first value in DataRangeuse nested For-Next loops to go through all theelements of DataRange -- take no action whenthe MaxVal is found -- check all other values againstthe current second maximum, and, if a new secondmaximum is found, update the stored value
611

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture