Created attachment 102371 [details] Very simple excel spreadsheet with VB macro Problem description: I get BASIC runtime error. '380' Incorrect property value when a simple spreadsheet calls this function in an xls spreadsheet (using LO 4.2.5.2 on Win7 Home Prem) Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Option Explicit Global Const DAYS_PER_YEAR As Double = 365.25 Global Const MONTHS_PER_YEAR As Double = 12# Public Function getCompound(initValue As Double, initDate As Date, finalDate As Date, intRate As Double) As Double Dim adjPeriod As Double adjPeriod = (finalDate - initDate) / DAYS_PER_YEAR getCompound = initValue * (1 + intRate) ^ adjPeriod End Function The macro works if I change VBASupport to 0 but the VBASupport line is added by LO and it is impractical to expect users to change code every time they open a Spreadsheet. What is it actually objecting to? The same spreadsheet works fine in OO 4.1.0 with Option VBASupport 1 Steps to reproduce: Open attached spreaheet with LO Calc Current behavior: BASIC runtime error. '380' Incorrect property value Expected behavior: Correct execution of function as on Excel and OO calc 4.1 Operating System: Windows 7 Version: 4.2.5.2 release
I got that error message in each LibO version I tested, the oldest being LibO 3.3.3 and the newer being 4.4.0.0.alpha0+ (*) (*) Build ID: b9dca968c6fd0ab5ca140c65b0e54d153cd34986 TinderBox: Win-x86@42, Branch:master, Time: 2014-07-18_22:51:20 I add BASIC expert to CC list to hear his opinion. that file loads fine in MS Excel Viewer
(In reply to comment #1) > I got that error message in each LibO version I tested, the oldest being > LibO 3.3.3 and the newer being 4.4.0.0.alpha0+ (*) > > (*) Build ID: b9dca968c6fd0ab5ca140c65b0e54d153cd34986 > TinderBox: Win-x86@42, Branch:master, Time: 2014-07-18_22:51:20 > > I add BASIC expert to CC list to hear his opinion. > > that file loads fine in MS Excel Viewer the problem seems related to handling of the Date type (and specifically when that type is passed as a param to a formula) iirc there is some special handling or conversion for Dates in VBA mode (or something... tbh it's a long time since I looked at that stuff) If you rewrite the macro signature without the type specifier for the Date types it should work in both excel and libreoffice e.g. Public Function getCompound(initValue As Double, initDate, finalDate, intRate As Double) As Double so initDate & finalDate are treated as Variant types
Thanks, The work around solves the problem in my test case and in my real, much more complicated spreadsheet. I can now suggest LO as valid alternative to excel for my users who dont have MS Office. Of course it would be good for the underlying problem to to be fixed so that others don't experience the same problem
Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.