diff -u -r ./old/sc/source/ui/vba/vbanames.cxx ./new/sc/source/ui/vba/vbanames.cxx
--- ./old/sc/source/ui/vba/vbanames.cxx	2009-10-15 22:39:56.000000000 +0800
+++ ./new/sc/source/ui/vba/vbanames.cxx	2009-10-21 16:42:24.000000000 +0800
@@ -2,7 +2,7 @@
  *
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  * 
- * Copyright 2008 by Sun Microsystems, Inc.
+ * Copyright 2009 by Sun Microsystems, Inc.
  *
  * OpenOffice.org - a multi-platform office productivity suite
  *
@@ -31,6 +31,9 @@
 
 #include <com/sun/star/table/XCellRange.hpp>
 #include <com/sun/star/sheet/XCellRangeAddressable.hpp>
+#include <ooo/vba/excel/XApplication.hpp>  //liuchen 2009-8-31
+#include <ooo/vba/excel/XWorkbook.hpp>  //liuchen 2009-8-31
+#include <ooo/vba/excel/XWorksheet.hpp>  //liuchen 2009-8-31
 
 #include "vbanames.hxx"
 #include "vbaname.hxx"
@@ -41,6 +44,7 @@
 #include <vcl/msgbox.hxx>
 #include "tabvwsh.hxx"
 #include "viewdata.hxx"
+#include "address.hxx" //liuchen 2009-8-31
 
 using namespace ::ooo::vba;
 using namespace ::com::sun::star;
@@ -90,6 +94,126 @@
 	return pViewData->GetDocument();
 }
 
+//liuchen 2009-8-31, resolve the defect of Names.Add
+enum ReferenceTypes
+{
+	Standard,
+	R1C1,
+	R1C1Loc
+};
+
+rtl::OUString 
+R1C1ToStd (const rtl::OUString R1C1)
+{
+	rtl::OUString strRow, strCol;
+	strRow = R1C1.copy(1, R1C1.lastIndexOf('C') - 1);
+	strCol = R1C1.copy(R1C1.lastIndexOf('C') + 1);
+	strCol = ScColToAlpha( static_cast<SCCOL>(strCol.toInt32()) - 1 );
+
+	return rtl::OUString('$') + strCol + rtl::OUString('$') + strRow;
+}
+
+bool IsR1C1Format(const rtl::OUString& strRange) //liuchen 2009-9-21, in Excel, Names.Add can handel the situation when the parameter "RefersTo" is a R1C1 format 
+{
+	//this function looks for the numbers between the first 'R' and 'C' in strRange, if find, return true
+	//for example, there is an 2 in "Sheet1!R2C3:R4C5"
+	sal_Int32 nStart = strRange.indexOf('!') < 0 ? 0 : strRange.indexOf('!');
+	sal_Int32 nR = strRange.toAsciiUpperCase().indexOf('R', nStart);
+	if (nR > -1)
+	{
+		sal_Int32 nC = strRange.toAsciiUpperCase().indexOf('C', nR);
+		if (nC > -1)
+		{
+			rtl::OUString strBetween = strRange.copy(nR + 1, nC - nR - 1);
+			if (strBetween.indexOf(':') > -1) //the form of "Sheet1!R2:C3" is not a R1C1 format  
+			{
+				return false;
+			}
+
+			try
+			{
+				sal_Int32 nNum = strBetween.toInt32();
+				if (nNum > 0)
+				{
+					return true;
+				}
+			}
+			catch (...)
+			{
+			}
+		}
+	}
+
+	return false;
+}
+
+rtl::OUString 
+GetRangeStr(const css::uno::Any& RefersTo, 
+			const enum ReferenceTypes& types) throw (css::uno::RuntimeException)
+{
+	rtl::OUString strRefersTo, strRange;
+
+	RefersTo >>= strRefersTo;
+	strRange = strRefersTo.copy(1);
+
+	if ( types == R1C1 || types == R1C1Loc || IsR1C1Format(strRange)) //liuchen 2009-8-31 maybe the code to process the situation of R1C1Loc should be different from the following code lines
+	{
+		if ( strRange.indexOf(':') < 0 ) //for example "sheet1!R1C1"
+		{			
+			strRange = strRange.copy(0, strRange.indexOf('!') + 1) + R1C1ToStd( strRange.copy( strRange.lastIndexOf('R') ) );
+		}
+		else //for example "sheet1!R1C1:R5C5"
+		{
+			strRange = strRange.copy(0, strRange.indexOf('!') + 1) 
+				+ R1C1ToStd( strRange.copy( strRange.indexOf('!') + 1 , strRange.indexOf(':') - strRange.indexOf('!') - 1 ) )
+				+ rtl::OUString(':')
+				+ R1C1ToStd( strRange.copy( strRange.indexOf(':') + 1 ) );
+		}
+	}	
+
+	return strRange;		
+}
+
+css::uno::Reference< excel::XRange >
+CalculateRange (const css::uno::Any& RefersTo,
+				const css::uno::Reference< ov::XHelperInterface >& xParent,
+				const enum ReferenceTypes& types) throw (css::uno::RuntimeException)
+{
+	uno::Reference< excel::XRange > xRange;
+	if ( RefersTo.getValueTypeClass() == uno::TypeClass_STRING )
+	{				
+		uno::Any aRange, aAny, aRet;
+		rtl::OUString strRange = GetRangeStr(RefersTo, types);
+
+		uno::Reference< excel::XApplication > xApp( xParent, ::uno::UNO_QUERY );
+		if ( xApp.get() )
+		{
+			aRange <<= strRange;
+			aRet = xApp->Range( aRange, aAny );
+			aRet >>= xRange;
+		}
+		else
+		{
+			uno::Reference< excel::XWorkbook > xWb( xParent, ::uno::UNO_QUERY );
+			if ( xWb.get() )
+			{				
+				uno::Any aSheet = xWb->Worksheets( uno::makeAny( strRange.copy(0, strRange.indexOf('!') ) ) );
+				uno::Reference< excel::XWorksheet > xSheet;
+				aSheet >>= xSheet;
+				aRange <<= strRange.copy(strRange.indexOf('!') + 1);
+				xRange = xSheet->Range( aRange, aAny );			
+			}
+		}
+	}
+	else
+	{
+		RefersTo >>= xRange;
+	}	
+
+	return xRange;
+}
+//liuchen 2009-8-31
+
 css::uno::Any
 ScVbaNames::Add( const css::uno::Any& Name ,
                                         const css::uno::Any& RefersTo,
@@ -130,11 +254,11 @@
 	if ( RefersTo.hasValue() || RefersToR1C1.hasValue() || RefersToR1C1Local.hasValue() )
 	{
 		if ( RefersTo.hasValue() )
-			RefersTo >>= xRange;
+			xRange = CalculateRange( RefersTo, mxParent, Standard ); //liuchen 2009-8-31
 		if ( RefersToR1C1.hasValue() )
-			RefersToR1C1 >>= xRange;
+			xRange = CalculateRange( RefersToR1C1, mxParent, R1C1 ); //liuchen 2009-8-31
 		if ( RefersToR1C1Local.hasValue() )
-			RefersToR1C1Local >>= xRange;
+			xRange = CalculateRange( RefersToR1C1Local, mxParent, R1C1Loc ); //liuchen 2009-8-31
 	}
 
 	if ( xRange.is() )
diff -u -r ./old/sc/source/ui/vba/vbarange.cxx ./new/sc/source/ui/vba/vbarange.cxx
--- ./old/sc/source/ui/vba/vbarange.cxx	2009-10-20 18:14:14.000000000 +0800
+++ ./new/sc/source/ui/vba/vbarange.cxx	2009-10-28 18:29:32.000000000 +0800
@@ -2,7 +2,7 @@
  *
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  * 
- * Copyright 2008 by Sun Microsystems, Inc.
+ * Copyright 2009 by Sun Microsystems, Inc.
  *
  * OpenOffice.org - a multi-platform office productivity suite
  *
@@ -1163,7 +1163,7 @@
 }
 
 
-table::CellRangeAddress getCellRangeAddressForVBARange( const uno::Any& aParam, ScDocShell* pDocSh,  formula::FormulaGrammar::AddressConvention aConv = formula::FormulaGrammar::CONV_XL_A1) throw ( uno::RuntimeException )
+table::CellRangeAddress getCellRangeAddressForVBARange( const uno::Any& aParam, ScDocShell* pDocSh, table::CellRangeAddress& pAddr, formula::FormulaGrammar::AddressConvention aConv = formula::FormulaGrammar::CONV_XL_A1) throw ( uno::RuntimeException )
 {
 	uno::Reference< table::XCellRange > xRangeParam;
 	switch ( aParam.getValueTypeClass() )
@@ -1174,6 +1174,8 @@
 			aParam >>= rString;
 			ScRangeList aCellRanges;
 			ScRange refRange;
+			//VBA by minz@cn.ibm.com. Set reference range.
+			ScUnoConversion::FillScRange( refRange, pAddr );
 			if ( getScRangeListForAddress ( rString, pDocSh, refRange, aCellRanges, aConv ) ) 			
 			{
 				if ( aCellRanges.First() == aCellRanges.Last() )
@@ -1470,6 +1472,19 @@
 	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );;
 }
 
+//Add by minz@cn.ibm.com. Support Range.FormulaLocal.
+uno::Any
+ScVbaRange::getFormulaLocal() throw (::com::sun::star::uno::RuntimeException)
+{
+	return getFormulaValue( formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
+}
+
+void
+ScVbaRange::setFormulaLocal(const uno::Any& rFormula ) throw (uno::RuntimeException)
+{
+	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );
+}
+
 uno::Any
 ScVbaRange::getFormulaR1C1() throw (::com::sun::star::uno::RuntimeException)
 {
@@ -1745,13 +1760,21 @@
 
 	//VBA, minz@cn.ibm.com
 	uno::Sequence< uno::Sequence<rtl::OUString> > aFmArray = xCellRangeFormula->getFormulaArray();
-	if( aFmArray.getLength() )
+	if( aFmArray.getLength() == 0 )
+		return aMatrix;
+	else if( aFmArray.getLength() == 1 && aFmArray[0].getLength() == 1 )
 	{
-		if( aFmArray.getLength() == 1 && aFmArray[0].getLength() == 1 )
-			aMatrix <<= aFmArray[0][0];
-		else	
-			aMatrix = xConverter->convertTo( uno::makeAny( xCellRangeFormula->getFormulaArray() ) , getCppuType((uno::Sequence< uno::Sequence< uno::Any > >*)0)  ) ;
-	}	
+		//liuchen 2009-9-23 the string got by excel Range.FormulaArray() does not contain brackets
+		rtl::OUString strMatrix = aFmArray[0][0];
+		if (strMatrix.indexOf('{') == 0 && strMatrix.lastIndexOf('}') == strMatrix.getLength() - 1)
+		{
+			aMatrix <<= strMatrix.copy(1, strMatrix.getLength() - 2);
+		}
+		//liuchen 2009-9-23
+
+		return aMatrix;
+	}
+	aMatrix = xConverter->convertTo( uno::makeAny( xCellRangeFormula->getFormulaArray() ) , getCppuType((uno::Sequence< uno::Sequence< uno::Any > >*)0)  ) ;
 	return aMatrix;
 }
 
@@ -1868,8 +1891,8 @@
 	if ( RelativeTo.hasValue() )
 	{
 		// #TODO should I throw an error if R1C1 is not set?
-		
-		table::CellRangeAddress refAddress = getCellRangeAddressForVBARange( RelativeTo, pDocShell );
+		table::CellRangeAddress refParentAddr;//no use
+		table::CellRangeAddress refAddress = getCellRangeAddressForVBARange( RelativeTo, pDocShell, refParentAddr );
 		dDetails = ScAddress::Details( formula::FormulaGrammar::CONV_XL_R1C1, static_cast< SCROW >( refAddress.StartRow ), static_cast< SCCOL >( refAddress.StartColumn ) );
 	}
 	aRange.Format( sRange,  nFlags, pDoc, dDetails ); 
@@ -2439,13 +2462,17 @@
 	}
 	else
 	{
+		//VBA by minz@cn.ibm.com. Set this range as reference range.
+		RangeHelper referRange( xReferrer );
+		table::CellRangeAddress referAddress = referRange.getCellRangeAddressable()->getRangeAddress();
+
 		table::CellRangeAddress  cell1, cell2;
-		cell1 = getCellRangeAddressForVBARange( Cell1, getScDocShell() ); 	
+		cell1 = getCellRangeAddressForVBARange( Cell1, getScDocShell(), referAddress ); 	
 		// Cell1 & Cell2 defined
 		// Excel seems to combine the range as the range defined by
 		// the combination of Cell1 & Cell2
 	
-		cell2 = getCellRangeAddressForVBARange( Cell2, getScDocShell() ); 	
+		cell2 = getCellRangeAddressForVBARange( Cell2, getScDocShell(), referAddress ); 	
 
 		resultAddress.StartColumn = ( cell1.StartColumn <  cell2.StartColumn ) ? cell1.StartColumn : cell2.StartColumn;
 		resultAddress.StartRow = ( cell1.StartRow <  cell2.StartRow ) ? cell1.StartRow : cell2.StartRow;
@@ -2962,7 +2989,8 @@
             uno::Reference< excel::XRange > xResultRange = new ScVbaRange( this, mxContext, xCellRange );
             if( xResultRange.is() )
             {
-                xResultRange->Select();
+				//VBA by minz@cn.ibm.com. No need to change focus to the found range.
+                //xResultRange->Select();
                 return xResultRange;
             }
         }
@@ -5155,6 +5183,7 @@
 	bool bIsSingleCell = isSingleCellRange(); 
 	bool bIsMultiArea = ( m_Areas->getCount() > 1 );
 	ScVbaRange* pRangeToUse = this;
+	uno::Reference< excel::XRange > xUsedRange;
 	sal_Int32 nType = 0;
 	if ( !( _oType >>= nType ) )
 		DebugHelper::exception(SbERR_BAD_PARAMETER, rtl::OUString() );
@@ -5170,7 +5199,7 @@
 		case excel::XlCellType::xlCellTypeConstants:
 		case excel::XlCellType::xlCellTypeFormulas:
 		case excel::XlCellType::xlCellTypeVisible:
-		case excel::XlCellType::xlCellTypeLastCell:
+//		case excel::XlCellType::xlCellTypeLastCell:
 		{
 			if ( bIsMultiArea )
 			{
@@ -5217,12 +5246,20 @@
 			}
 			else if ( bIsSingleCell )
 			{
-				uno::Reference< excel::XRange > xUsedRange = getWorksheet()->getUsedRange();
-				pRangeToUse = static_cast< ScVbaRange* >( xUsedRange.get() );	
+				xUsedRange = getWorksheet()->getUsedRange();
+				pRangeToUse = dynamic_cast< ScVbaRange* >( xUsedRange.get() );	
 			}
 		
 			break;
-		}			
+		}	
+		//the last cell in the used range 
+		case excel::XlCellType::xlCellTypeLastCell:
+		{
+			xUsedRange = getWorksheet()->getUsedRange();
+			pRangeToUse = dynamic_cast< ScVbaRange* >( xUsedRange.get() );	
+		}
+			break;
+
 		default:
 		DebugHelper::exception(SbERR_BAD_PARAMETER, rtl::OUString() );
 			break;
@@ -5295,6 +5332,7 @@
 			}
 			case excel::XlCellType::xlCellTypeLastCell:
 				xRange = Cells( uno::makeAny( getCount() ), uno::Any() );
+				break;
 			case excel::XlCellType::xlCellTypeVisible:
 				xLocSheetCellRanges = xQuery->queryVisibleCells();	      
 				break;
diff -u -r ./old/sc/source/ui/vba/vbarange.hxx ./new/sc/source/ui/vba/vbarange.hxx
--- ./old/sc/source/ui/vba/vbarange.hxx	2009-10-15 22:39:54.000000000 +0800
+++ ./new/sc/source/ui/vba/vbarange.hxx	2009-10-28 18:24:14.000000000 +0800
@@ -2,7 +2,7 @@
  *
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  * 
- * Copyright 2008 by Sun Microsystems, Inc.
+ * Copyright 2009 by Sun Microsystems, Inc.
  *
  * OpenOffice.org - a multi-platform office productivity suite
  *
@@ -142,6 +142,8 @@
 	virtual void   SAL_CALL setFormula( const css::uno::Any& rFormula ) throw (css::uno::RuntimeException);
 	virtual css::uno::Any SAL_CALL getFormulaArray() throw (css::uno::RuntimeException);
 	virtual void   SAL_CALL setFormulaArray(const css::uno::Any& rFormula) throw (css::uno::RuntimeException);
+	virtual css::uno::Any SAL_CALL getFormulaLocal() throw (css::uno::RuntimeException);
+	virtual void   SAL_CALL setFormulaLocal( const css::uno::Any &rFormula ) throw (css::uno::RuntimeException);
 	virtual css::uno::Any SAL_CALL getFormulaR1C1() throw (css::uno::RuntimeException);
 	virtual void   SAL_CALL setFormulaR1C1( const css::uno::Any &rFormula ) throw (css::uno::RuntimeException);
 	virtual ::sal_Int32 SAL_CALL getCount() throw (css::uno::RuntimeException);
diff -u -r ./old/sc/source/ui/vba/vbaworksheet.cxx ./new/sc/source/ui/vba/vbaworksheet.cxx
--- ./old/sc/source/ui/vba/vbaworksheet.cxx	2009-10-15 22:39:52.000000000 +0800
+++ ./new/sc/source/ui/vba/vbaworksheet.cxx	2009-10-28 18:32:08.000000000 +0800
@@ -2,7 +2,7 @@
  *
  * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
  * 
- * Copyright 2008 by Sun Microsystems, Inc.
+ * Copyright 2009 by Sun Microsystems, Inc.
  *
  * OpenOffice.org - a multi-platform office productivity suite
  *
@@ -347,7 +347,8 @@
 	xUsedCursor->gotoStartOfUsedArea( false );
 	xUsedCursor->gotoEndOfUsedArea( true );
 	uno::Reference< table::XCellRange > xRange( xSheetCellCursor, uno::UNO_QUERY);
-	return new ScVbaRange(this, mxContext, xRange);
+	//return new ScVbaRange(this, mxContext, xRange);
+	return uno::Reference< excel::XRange >( new ScVbaRange(this, mxContext, xRange) );
 }
 
 uno::Reference< excel::XOutline >