Commits

Daniel Rentz [dr]  committed 2af8347

mib19: #163666# fire Worksheet_Change event when using the Range.Value, Range.Formula, Range.Clear[Contents] symbols

  • Participants
  • Parent commits b9e6174

Comments (0)

Files changed (3)

File sc/source/ui/vba/vbaeventshelper.cxx

 namespace {
 
 /** Extracts a sheet index from the specified element of the passed sequence.
-    The element may be an integer, or a Calc range or ranges object. */
+    The element may be an integer, a Calc range or ranges object, or a VBA Range object. */
 SCTAB lclGetTabFromArgs( const uno::Sequence< uno::Any >& rArgs, sal_Int32 nIndex ) throw (lang::IllegalArgumentException)
 {
     VbaEventsHelperBase::checkArgument( rArgs, nIndex );
     if( rArgs[ nIndex ] >>= nTab )
         return nTab;
 
-    // next, try single range object
+    // try VBA Range object
+    uno::Reference< excel::XRange > xVbaRange = getXSomethingFromArgs< excel::XRange >( rArgs, nIndex );
+    if( xVbaRange.is() )
+    {
+        uno::Reference< XHelperInterface > xVbaHelper( xVbaRange, uno::UNO_QUERY_THROW );
+        // TODO: in the future, the parent may be an excel::XChart (chart sheet) -> will there be a common base interface?
+        uno::Reference< excel::XWorksheet > xVbaSheet( xVbaHelper->getParent(), uno::UNO_QUERY_THROW );
+        // VBA sheet index is 1-based
+        return static_cast< SCTAB >( xVbaSheet->getIndex() - 1 );
+    }
+
+    // try single UNO range object
     uno::Reference< sheet::XCellRangeAddressable > xCellRangeAddressable = getXSomethingFromArgs< sheet::XCellRangeAddressable >( rArgs, nIndex );
     if( xCellRangeAddressable.is() )
         return xCellRangeAddressable->getRangeAddress().Sheet;
 
-    // at last, try range list
+    // at last, try UNO range list
     uno::Reference< sheet::XSheetCellRangeContainer > xRanges = getXSomethingFromArgs< sheet::XSheetCellRangeContainer >( rArgs, nIndex );
     if( xRanges.is() )
     {
         aChange.ReplacedElement >>= xRangeObj;
         if( xRangeObj.is() )
         {
-            uno::Sequence< uno::Any > aArgs(1);
+            uno::Sequence< uno::Any > aArgs( 1 );
             aArgs[0] <<= xRangeObj;
             mrVbaEvents.processVbaEvent( WORKSHEET_CHANGE, aArgs );
         }
 uno::Any ScVbaEventsHelper::createRange( const uno::Sequence< uno::Any >& rArgs, sal_Int32 nIndex ) const
         throw (lang::IllegalArgumentException, uno::RuntimeException)
 {
-    uno::Reference< sheet::XSheetCellRangeContainer > xRanges = getXSomethingFromArgs< sheet::XSheetCellRangeContainer >( rArgs, nIndex );
-    uno::Reference< table::XCellRange > xRange = getXSomethingFromArgs< table::XCellRange >( rArgs, nIndex );
-    if ( !xRanges.is() && !xRange.is() )
-        throw lang::IllegalArgumentException();
+    // it is possible to pass an existing VBA Range object
+    uno::Reference< excel::XRange > xVbaRange = getXSomethingFromArgs< excel::XRange >( rArgs, nIndex );
+    if( !xVbaRange.is() )
+    {
+        uno::Reference< sheet::XSheetCellRangeContainer > xRanges = getXSomethingFromArgs< sheet::XSheetCellRangeContainer >( rArgs, nIndex );
+        uno::Reference< table::XCellRange > xRange = getXSomethingFromArgs< table::XCellRange >( rArgs, nIndex );
+        if ( !xRanges.is() && !xRange.is() )
+            throw lang::IllegalArgumentException();
 
-    uno::Sequence< uno::Any > aArgs( 2 );
-    if ( xRanges.is() )
-    {
-        aArgs[ 0 ] <<= excel::getUnoSheetModuleObj( xRanges );
-        aArgs[ 1 ] <<= xRanges;
+        uno::Sequence< uno::Any > aArgs( 2 );
+        if ( xRanges.is() )
+        {
+            aArgs[ 0 ] <<= excel::getUnoSheetModuleObj( xRanges );
+            aArgs[ 1 ] <<= xRanges;
+        }
+        else
+        {
+            aArgs[ 0 ] <<= excel::getUnoSheetModuleObj( xRange );
+            aArgs[ 1 ] <<= xRange;
+        }
+        xVbaRange.set( createVBAUnoAPIServiceWithArgs( mpShell, "ooo.vba.excel.Range", aArgs ), uno::UNO_QUERY_THROW );
     }
-    else
-    {
-        aArgs[ 0 ] <<= excel::getUnoSheetModuleObj( xRange );
-        aArgs[ 1 ] <<= xRange;
-    }
-    uno::Reference< uno::XInterface > xVbaRange( createVBAUnoAPIServiceWithArgs( mpShell, "ooo.vba.excel.Range", aArgs ), uno::UNO_SET_THROW );
     return uno::Any( xVbaRange );
 }
 

File sc/source/ui/vba/vbarange.cxx

  * for a copy of the LGPLv3 License.
  *
  ************************************************************************/
+ 
+#include "vbarange.hxx"
+
 #include <vbahelper/helperdecl.hxx>
 
 #include <comphelper/unwrapargs.hxx>
 #include <sfx2/objsh.hxx>
 
 #include <com/sun/star/script/ArrayWrapper.hpp>
+#include <com/sun/star/script/vba/VBAEventId.hpp>
+#include <com/sun/star/script/vba/XVBAEventProcessor.hpp>
 #include <com/sun/star/sheet/XDatabaseRange.hpp>
 #include <com/sun/star/sheet/XDatabaseRanges.hpp>
 #include <com/sun/star/sheet/XGoalSeek.hpp>
 #include <globstr.hrc>
 #include <unonames.hxx>
 
-#include "vbarange.hxx"
+#include "vbaapplication.hxx"
 #include "vbafont.hxx"
 #include "vbacomment.hxx"
 #include "vbainterior.hxx"
 	return pDataSet;
 }
 
+void ScVbaRange::fireChangeEvent()
+{
+    if( ScVbaApplication::getDocumentEventsEnabled() )
+    {
+        if( ScDocument* pDoc = getScDocument() )
+        {
+            uno::Reference< script::vba::XVBAEventProcessor > xVBAEvents = pDoc->GetVbaEventProcessor();
+            if( xVBAEvents.is() ) try
+            {
+                uno::Sequence< uno::Any > aArgs( 1 );
+                aArgs[ 0 ] <<= uno::Reference< excel::XRange >( this );
+                xVBAEvents->processVbaEvent( script::vba::VBAEventId::WORKSHEET_CHANGE, aArgs );
+            }
+            catch( uno::Exception& )
+            {
+            }
+        }
+    }
+}
+
 class SingleRangeEnumeration : public EnumerationHelper_BASE
 {
     uno::Reference< XHelperInterface > m_xParent;
 
 
 void 
-ScVbaRange::setValue(  const uno::Any  &aValue,  ValueSetter& valueSetter ) throw (uno::RuntimeException)
+ScVbaRange::setValue( const uno::Any& aValue, ValueSetter& valueSetter, bool bFireEvent ) throw (uno::RuntimeException)
 {
 	uno::TypeClass aClass = aValue.getValueTypeClass();
 	if ( aClass == uno::TypeClass_SEQUENCE )
 	{
 		visitArray( valueSetter );
 	}
+    if( bFireEvent ) fireChangeEvent();
 }
 
 void SAL_CALL
 		return;
 	}	
 	CellValueSetter valueSetter( aValue );
-	setValue( aValue, valueSetter );
-}
-
-void
+	setValue( aValue, valueSetter, true );
+}
+
+void SAL_CALL
 ScVbaRange::Clear() throw (uno::RuntimeException)
 {
     using namespace ::com::sun::star::sheet::CellFlags;
 	sal_Int32 nFlags = VALUE | DATETIME | STRING | FORMULA | HARDATTR | EDITATTR | FORMATTED;
-	ClearContents( nFlags );
+	ClearContents( nFlags, true );
 }
 
 //helper ClearContent
 void
-ScVbaRange::ClearContents( sal_Int32 nFlags ) throw (uno::RuntimeException)
+ScVbaRange::ClearContents( sal_Int32 nFlags, bool bFireEvent ) throw (uno::RuntimeException)
 {
 	// #TODO code within the test below "if ( m_Areas.... " can be removed
 	// Test is performed only because m_xRange is NOT set to be
 			uno::Reference< excel::XRange > xRange( m_Areas->Item( uno::makeAny(index), uno::Any() ), uno::UNO_QUERY_THROW );
 			ScVbaRange* pRange = getImplementation( xRange );
 			if ( pRange )
-				pRange->ClearContents( nFlags );	
+				pRange->ClearContents( nFlags, false ); // do not fire for single ranges
 		}
+        // fire change event for the entire range list
+        if( bFireEvent ) fireChangeEvent();
 		return;
 	}
 
 
 	uno::Reference< sheet::XSheetOperation > xSheetOperation(mxRange, uno::UNO_QUERY_THROW);
 	xSheetOperation->clearContents( nFlags );
-}
+    if( bFireEvent ) fireChangeEvent();
+}
+
+void SAL_CALL
+ScVbaRange::ClearComments() throw (uno::RuntimeException)
+{
+	ClearContents( sheet::CellFlags::ANNOTATION, false );
+}
+
+void SAL_CALL
+ScVbaRange::ClearContents() throw (uno::RuntimeException)
+{
+    using namespace ::com::sun::star::sheet::CellFlags;
+	sal_Int32 nFlags = VALUE | STRING |  DATETIME | FORMULA;
+	ClearContents( nFlags, true );
+}
+
+void SAL_CALL
+ScVbaRange::ClearFormats() throw (uno::RuntimeException)
+{
+	//FIXME: need to check if we need to combine FORMATTED
+    using namespace ::com::sun::star::sheet::CellFlags;
+	sal_Int32 nFlags = HARDATTR | FORMATTED | EDITATTR;
+	ClearContents( nFlags, false );
+}
+
 void
-ScVbaRange::ClearComments() throw (uno::RuntimeException)
-{
-	ClearContents( sheet::CellFlags::ANNOTATION );
-}
-
-void
-ScVbaRange::ClearContents() throw (uno::RuntimeException)
-{
-	sal_Int32 nClearFlags = ( sheet::CellFlags::VALUE |
-		sheet::CellFlags::STRING |  sheet::CellFlags::DATETIME | 
-		sheet::CellFlags::FORMULA );
-	ClearContents( nClearFlags );
-}
-
-void
-ScVbaRange::ClearFormats() throw (uno::RuntimeException)
-{
-	//FIXME: need to check if we need to combine sheet::CellFlags::FORMATTED
-	sal_Int32 nClearFlags = sheet::CellFlags::HARDATTR | sheet::CellFlags::FORMATTED | sheet::CellFlags::EDITATTR;
-	ClearContents( nClearFlags );
-}
-
-void
-ScVbaRange::setFormulaValue( const uno::Any& rFormula, formula::FormulaGrammar::Grammar eGram ) throw (uno::RuntimeException)
+ScVbaRange::setFormulaValue( const uno::Any& rFormula, formula::FormulaGrammar::Grammar eGram, bool bFireEvent ) throw (uno::RuntimeException)
 {
 	// If this is a multiple selection apply setFormula over all areas
 	if ( m_Areas->getCount() > 1 )
 		return;
 	}	
 	CellFormulaValueSetter formulaValueSetter( rFormula, getScDocument(), eGram );
-	setValue( rFormula, formulaValueSetter );
+	setValue( rFormula, formulaValueSetter, bFireEvent );
 }
 
 uno::Any 
 ScVbaRange::setFormula(const uno::Any &rFormula ) throw (uno::RuntimeException)
 {
 	// #FIXME converting "=$a$1" e.g. CONV_XL_A1 -> CONV_OOO                        	// results in "=$a$1:a1", temporalily disable conversion
-	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1 );;
+	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_A1, true );
 }
 
 uno::Any
 void
 ScVbaRange::setFormulaR1C1(const uno::Any& rFormula ) throw (uno::RuntimeException)
 {
-	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1 );
+	setFormulaValue( rFormula,formula::FormulaGrammar::GRAM_NATIVE_XL_R1C1, true );
 }
 
 uno::Any
 		throw uno::RuntimeException( rtl::OUString( RTL_CONSTASCII_USTRINGPARAM("That command cannot be used on multiple selections" ) ), uno::Reference< uno::XInterface >() );
 
 	sal_Int16 nDataOption1 = excel::XlSortDataOption::xlSortNormal;
-	sal_Int16 nDataOption2 = excel::XlSortDataOption::xlSortNormal;;
+	sal_Int16 nDataOption2 = excel::XlSortDataOption::xlSortNormal;
 	sal_Int16 nDataOption3 = excel::XlSortDataOption::xlSortNormal;
 
 	ScDocument* pDoc = getScDocument();
 	// Use the normal uno api, sometimes e.g. when you want to use ALL as the filter
 	// we can't use refresh as the uno interface doesn't have a concept of ALL
 	// in this case we just call the core calc functionality - 
-	bool bAll = false;;
+	bool bAll = false;
 	if ( ( Field >>= nField )  )
 	{
         uno::Reference< sheet::XSheetFilterDescriptor2 > xDesc(

File sc/source/ui/vba/vbarange.hxx

 	sal_Bool mbIsRows;
 	sal_Bool mbIsColumns;
 	css::uno::Reference< ov::excel::XValidation > m_xValidation;
+
 	double getCalcColWidth( const css::table::CellRangeAddress& ) throw (css::uno::RuntimeException);
 	double getCalcRowHeight( const css::table::CellRangeAddress& ) throw (css::uno::RuntimeException);
 	void visitArray( ArrayVisitor& vistor );
 
 	void fillSeries(  css::sheet::FillDirection nFillDirection, css::sheet::FillMode nFillMode, css::sheet::FillDateMode nFillDateMode, double fStep, double fEndValue ) throw( css::uno::RuntimeException );	 
 
-	void ClearContents( sal_Int32 nFlags ) throw (css::uno::RuntimeException);
-	virtual void   setValue( const css::uno::Any& aValue, ValueSetter& setter) throw ( css::uno::RuntimeException);
-	virtual css::uno::Any getValue( ValueGetter& rValueGetter ) throw (css::uno::RuntimeException);
-	virtual css::uno::Any getFormulaValue( formula::FormulaGrammar::Grammar ) throw (css::uno::RuntimeException);
-	virtual void   setFormulaValue( const css::uno::Any& aValue, formula::FormulaGrammar::Grammar ) throw ( css::uno::RuntimeException);
+	void ClearContents( sal_Int32 nFlags, bool bFireEvent ) throw (css::uno::RuntimeException);
+
+	css::uno::Any getValue( ValueGetter& rValueGetter ) throw (css::uno::RuntimeException);
+	void setValue( const css::uno::Any& aValue, ValueSetter& setter, bool bFireEvent ) throw ( css::uno::RuntimeException);
+
+	css::uno::Any getFormulaValue( formula::FormulaGrammar::Grammar ) throw (css::uno::RuntimeException);
+	void setFormulaValue( const css::uno::Any& aValue, formula::FormulaGrammar::Grammar, bool bFireEvent ) throw ( css::uno::RuntimeException);
+
 	css::uno::Reference< ov::excel::XRange > getArea( sal_Int32 nIndex  ) throw( css::uno::RuntimeException );
 	ScCellRangeObj* getCellRangeObj( )  throw ( css::uno::RuntimeException );
     ScCellRangesObj* getCellRangesObj() throw ( css::uno::RuntimeException );
  	css::uno::Reference< ov::excel::XRange > PreviousNext( bool bIsPrevious );
  	css::uno::Reference< ov::excel::XRange > SpecialCellsImpl( sal_Int32 nType, const css::uno::Any& _oValue) throw ( css::script::BasicErrorException );
 	css::awt::Point getPosition() throw ( css::uno::RuntimeException );
+
+    /** Fires a Worksheet_Change event for this range or range list. */
+	void fireChangeEvent();
+
 protected:
 	virtual ScCellRangesBase* getCellRangesBase() throw ( css::uno::RuntimeException );
 	virtual SfxItemSet* getCurrentDataSet( )  throw ( css::uno::RuntimeException );