Tips, Tricks and Traps (More) Excel.Application & Excel.Range

Questions

1.       How to stop the screen from flickering during a long-running program?

2.       What statement requires that the programmer explicitly type each variable?

3.       What method of the range object can be used to extend the number of columns in a range from 3 to 6?

4.       What property of the range object would put 5 in cell $A$1?

5.       What function displays both a message and icons like the one in Figure 1 to the user?

   Figure 1

  

6.       What function determines whether a cell is populated with data?

7.       How to set the default indexing of an array to start at 1?

8.       What method of the Application prevents recursively calling an event’s code written for a Sheets, Workbook or Application object's event?

9.       What property of the Application object suppresses the warning message that Excel display’s when certain action- like OverWriting a file are performed

10.   What is the method of the range object that will select all the cells from A1 to C3?

11.   Bonus: If data has been copied to the clipboard, what method of the range object would copy it’s contents to a new range?
 

 

Answer Key

1.       Application.ScreenUpdating =False

2.       Option Explicit

3.       Resize method

4.       Value property

5.       MessageBox function

6.       IsEmpty()

7.       Option Base 1

8.       Application.EnableEvents=False

9.       Application.DisplayAlerts property=False

10.       Range(“A1:C3”).Select

11.       Range.PasteSpecial

Excel Range Tips, Tricks and Traps

 
1. How to refer to a range using A1 notation
Range("A1:A5").Copy Range("B1")  2. How to put a value in a range(cell)
Range("C1").Value="Hello world"  3. How to create a multi-cell range
Range("A1:G5").Select  4. How to refer to a multi-cell range in a particular worksheet
Worksheets(1).Range("A1,A5,B4:B8").Select  5. How to refer to the currently active cell
? ActiveCell.Address  6. How to find the parent object (worksheet) of a range object
? ActiveCell.Parent.Name  7. How to refer to a range in the workbook that currently has focus 
              ActiveWorkbook.Worksheets(1).Range("A1:C2").Select 

8. How to refer to a range In the workbook that contains the code
ThisWorkbook.Worksheets(1).Range("C2").Select  9. How to add a formula to a range Range("B4").Formula="=45*10"  10. How to determine if a cell has a formula
? ActiveCell.HasFormula

(More) Tips, Tricks and Traps for the Excel.Range

 

1.       How to determine the address of a  range

? ActiveCell.Address()

 

2.       How to remove  formatting  in a range(cell)

ActiveCell.ClearFormats

 

3.       How to create a union of ranges(merge cells)

Range("A1:G5").Select

 

4.       How to select an entire column of range

Range("A1:D5").Columns(2).Select

 

5.       How to select an entire row of range

Range("A1:D5").Rows(3).Select

 

6.       How to test if a cell is Not populated with data

?  IsEmpty(ActiveCell.Value)

 

7.       How to test to see if cell is populated with a valid date

?  IsDate(ActiveCell.Value)

                               

8.       How to determine if a cell contains number(s)

? IsNumeric("hello")

 

9.       How to  copy the content of  a cell to another range

Activecell.Copy Destination:=ActiveCell.Offset(2,1)

 

10.   How to perform a PasteSpecial  operation from the contents copied to the Clipboard (via Control+C)

Activecell.Copy

ActiveCell.Offset(4,1).PasteSpecial(xlPasteAll)