Page 150 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

Inside Out

The FormulaLocal Property and Local Settings
The FormulaLocal property returns or sets a formula in the language specified in the active
Windows user’s regional settings. For example, if a user types the formula =SOMME(B3:B15)
into an input box (SOMME is the French version of SUM), you can assign the formula to cell
B16 on the first worksheet in your workbook using the following statement:

Range(B16).FormulaLocal = InputBox("Enter a formula.")

Table 6-2. Cell Error Values
Error Value Return
##### Error 2015
#DIV/0! Error 2007
#N/A Error 2042
#NAME? Error 2029
#NULL! Error 2000
#NUM! Error 2036
#REF! Error 2023
#VALUE! Error 2015

Intersect Method
The Intersect method compares two or more ranges to determine if they overlap or share any
common cells. Figure 6-5 shows one range bounded at B3:E6 and a second range bounded at
D5:G8. The intersection of the two would be the range D5:E6 as illustrated in the following
example:
Sub DisplayIntersection()
Range("B3:E6").BorderAround Color:=vbBlack, Weight:=xlThick
Range("B3:E6").Select
Set rge1 = Selection
Range("D5:E8").BorderAround Color:=vbYellow, Weight:=xlThick
Range("D5:E8").Select
Set rge2 = Selection
Set myRange = Application.Intersect(rge1, rge2)
Range(myRange.Address).Select
Selection.Interior.Color = vbBlue
End Sub

Chapter 6

124