[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

 copy a working macro to another workbook then macro fails Goto page 1, 2  Next
Author Message
eekfonky23
Power User

Joined: 18 May 2007
Posts: 55
Location: Scotland

Posted: Sun Jul 15, 2007 11:35 am    Post subject: copy a working macro to another workbook then macro fails

I have this VBA macro set up to do some customs functions for me. It works fine on one workbook and when I copy it across the macro doesn't work. It has a 'data type mismathc' and I don't know why? The mismatch is on:
Function TVA(n)
TVA = (n / 100) * TVARate
End Function
here is the whole thing:
 Code: Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 'Declares Duty Rates in Hectolitres, Litres & Bottles as Constants Public Const VATRateA As Double = 0, VATRateB As Double = 5, VATRate As Double = 17.5 Public Const TVARateA As Double = 0, TVARateB As Double = 5.5, TVARate As Double = 19.6 Public Const HectSt As Currency = 177.99, HectSp As Currency = 227.99 Public Const LitreSt As Currency = HectSt / 100, LitreSp As Currency = HectSp / 100 Public Const BtlSt As Currency = LitreSt * 0.75, BtlSp As Currency = LitreSp * 0.75 Function DutySt(n)     DutySt = n * BtlSt End Function Function DutySp(n)     DutySp = n * BtlSp End Function Function VAT(n)     VAT = (n / 100) * VATRate End Function Function VATA(n)     VATA = (n / 100) * VATRateA End Function Function VATB(n)     VATB = (n / 100) * VATRateB End Function Function TVAA(n)     TVAA = (n / 100) * TVARateA End Function Function TVAB(n)     TVAB = (n / 100) * TVARateB End Function Function TVA(n)     TVA = (n / 100) * TVARate End Function Function LTVA(n)     LTVA = n / 1.196 End Function Function LTVAA(n)     LTVAA = n / 0 End Function Function LTVAB(n)     LTVAB = n / 1.055 End Function Function LVATA(n)     LVATA = n / 0 End Function Function LVATB(n)     LVATB = n / 1.05 End Function Function LVAT(n)     LVAT = n / 1.175 End Function
swingkyd

Joined: 15 Sep 2004
Posts: 479

Posted: Thu Jul 19, 2007 12:03 pm    Post subject:

I loaded this code and it seems to work correctly. I suspect there is problems with the way you are calling the functions.
I loaded the macro with the following code:
 Code: Sub test1   Print TVA(2) end Sub

which produces 0.392.
swingkyd

Joined: 15 Sep 2004
Posts: 479

 Posted: Thu Jul 19, 2007 12:04 pm    Post subject: Maybe you can explain what you mean by "copying across workbooks."
eekfonky23
Power User

Joined: 18 May 2007
Posts: 55
Location: Scotland

 Posted: Wed Jul 25, 2007 7:34 am    Post subject: when I use my OO Ubuntu 2.2 to use a macro it works just fine when my colleague tries to use it with OO 2.2 for Windows it doesn't work. I have emailed the file back to myself and have no problem yet with the windows version no joy. Any explanations?
swingkyd

Joined: 15 Sep 2004
Posts: 479

 Posted: Wed Jul 25, 2007 7:49 am    Post subject: maybe your colleague does not know how to run it? My windows OO.org 2.2.1 seems to work fine. How exactly is your colleague calling the macro? Did he copy and paste it, did you export the macro library and they import it using the extension manager?
eekfonky23
Power User

Joined: 18 May 2007
Posts: 55
Location: Scotland

 Posted: Wed Jul 25, 2007 8:19 am    Post subject: The macro is called by a button (and the macros are enabled). I tried it myself via remote access and it simply doesn't work - yet the exact same file works in ubuntu, very strange!
Mark B
Super User

Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

 Posted: Wed Jul 25, 2007 10:44 am    Post subject: Are you sure that the macro is embedded in the spreadsheet and not just in your own 'My Macros'? Mark_________________Mark B's Articles
eekfonky23
Power User

Joined: 18 May 2007
Posts: 55
Location: Scotland

 Posted: Wed Jul 25, 2007 10:49 am    Post subject: I'm sure it's embedded in the file, it partly works but he final part to display the information doesn't work in only the Windows version
swingkyd

Joined: 15 Sep 2004
Posts: 479

 Posted: Wed Jul 25, 2007 11:30 am    Post subject: you could try posting the actual file and have us look at it.
eekfonky23
Power User

Joined: 18 May 2007
Posts: 55
Location: Scotland

Posted: Wed Jul 25, 2007 3:43 pm    Post subject:

here is the macro, sheet it relates to can be forwarded to you
 Code: Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Sub DutyEst()         'Defines Variables     Dim TotSt As Variant, TotSp As Variant, NumSt As Variant, NumSp As Variant     Dim HectoSt As Variant, HectoSp As Variant, LitresSt As Variant     Dim LitresSp As Variant, Ans As Variant, Title As String, WrAns As String     Dim DutySt As Variant, DutySp As Variant, CartonSt As Variant, CartonSp As Variant     Dim BottleSt As Variant, BottleSp As Variant     Title = "Customs Estimate"     WrAns = "You MUST enter a number!!"     'Sets up Input Box for number of cartons still     NumSt = InputBox("Enter number of Cartons of STILL wine", "Customs Estimate")         'If no number is entered or Cancel clicked then restarts sub         If IsNumeric(NumSt) Then                 NumSt = Int(NumSt)         Else:                 MsgBox WrAns             Exit Sub         End If         'Sets up Input Box for number of cartons sparkling     NumSp = InputBox("Enter number of Cartons of SPARKLING wine", "Customs Estimate")             'If no number is entered or Cancel clicked then restarts sub         If IsNumeric(NumSp) Then                 NumSp = Int(NumSp)         Else:                 MsgBox WrAns             Exit Sub         End If          'Converts NumSt & NumSp into Litres & Hectolitres             BottlesSt = NumSt * 6             BottlesSp = NumSp * 6             LitresSt = NumSt * 4.5             LitresSp = NumSp * 4.5             HectoSt = LitresSt / 100             HectoSp = LitresSp / 100         'Multiplies cartons by duty rate      oSheet = ThisComponent.getSheets.getByName("Rates")      dCellContent = oSheet.getCellRangeByName("A4").getValue      TotSt = BottlesSt * dCellContent      oSheet = ThisComponent.getSheets.getByName("Rates")      dCellContent = oSheet.getCellRangeByName("A6").getValue      TotSp = BottlesSp * dCellContent           'Displays duty in a message box     Ans = MsgBox("Duty Still = £" & TotSt & vbNewLine _             & "Duty Sparkling = £" & TotSp & vbNewLine & vbNewLine _             & "Duty Total = £" & TotSt + TotSp & vbNewLine & vbNewLine _             & "               Continue?", vbYesNo, Title)         If Ans = vbYes Then                  'Displays Information in cell range E15:I16 if Yes box clicked             Worksheets("Rates").Range("E15").Value = HectoSt             Worksheets("Rates").Range("E16").Value = HectoSp             Worksheets("Rates").Range("H15").Value = NumSt             Worksheets("Rates").Range("H16").Value = NumSp             Worksheets("Rates").Range("G15").Value = TotSt             Worksheets("Rates").Range("G16").Value = TotSp             Worksheets("Rates").Range("I15").Value = BottlesSt             Worksheets("Rates").Range("I16").Value = BottlesSp                     End If         'Restarts if No box clicked         If Ans = vbNo Then             Exit Sub         End If  End Sub
Mark B
Super User

Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

Posted: Wed Jul 25, 2007 11:36 pm    Post subject: Re: copy a working macro to another workbook then macro fail

I should have spotted this from your OP:

 eekfonky23 wrote: I have this VBA macro set up to do some customs functions for me.

VBA is not universally supported in OOo, and so I would guess that you're using a version of OOo that does support VBA, whilst your colleague is using one that doesn't. For instance your macro works fine on my SUSE version of 2.0.4 OOo, but not in my 2.2.0 version of OOo from the OpenOffice.org web site.

You've therefore got two solutions:

• Rewrite all of the VBA parts of your code to standard OOoBasic
• Change your colleagues version of OOo to one that does support VBA (such as the Novell version)

Mark
_________________
Mark B's Articles
swingkyd

Joined: 15 Sep 2004
Posts: 479

Posted: Thu Jul 26, 2007 7:55 am    Post subject: Re: copy a working macro to another workbook then macro fail

 Mark B wrote: You've therefore got two solutions: Rewrite all of the VBA parts of your code to standard OOoBasic Change your colleagues version of OOo to one that does support VBA (such as the Novell version) Mark

I see your point Mark, but it works for me as written on my MS Windows version 2.2.1 from the OO.org site. Maybe everyone should update to the latest version?
swingkyd

Joined: 15 Sep 2004
Posts: 479

Posted: Thu Jul 26, 2007 8:17 am    Post subject:

The problem with the code is here:
 Code: Ans = MsgBox("Duty Still = £" & TotSt & vbNewLine _             & "Duty Sparkling = £" & TotSp & vbNewLine & vbNewLine _             & "Duty Total = £" & TotSt + TotSp & vbNewLine & vbNewLine _             & "               Continue?", vbYesNo, Title)         If Ans = vbYes Then                  'Displays Information in cell range E15:I16 if Yes box clicked             Worksheets("Rates").Range("E15").Value = HectoSt             Worksheets("Rates").Range("E16").Value = HectoSp             Worksheets("Rates").Range("H15").Value = NumSt             Worksheets("Rates").Range("H16").Value = NumSp             Worksheets("Rates").Range("G15").Value = TotSt             Worksheets("Rates").Range("G16").Value = TotSp             Worksheets("Rates").Range("I15").Value = BottlesSt             Worksheets("Rates").Range("I16").Value = BottlesSp                     End If         'Restarts if No box clicked         If Ans = vbNo Then             Exit Sub         End If

The variables vbNo, vbYes, vbYesNo are not declared and are simply variants.
It would be best to read the documentation on the MsgBox function call in OO.org and re-work your code. Also, the method of calling your sheets "Worksheets("Rates").Range...etc will not work correctly in StarBasic. you are close but not quite there.
{edit} but this code is not the same as the code you originally posted.
swingkyd

Joined: 15 Sep 2004
Posts: 479

Posted: Thu Jul 26, 2007 9:38 am    Post subject:

Just for fun, I translated the code for you. It's not pretty but it does what I think you wanted. Hopefully this will help you. try this code and see if you and your colleague can run it correctly.
 Code: Rem Attribute VBA_ModuleType=VBAModule Option VBASupport 1 Sub DutyEst()         'Defines Variables     Dim TotSt As Variant, TotSp As Variant, NumSt As Variant, NumSp As Variant     Dim HectoSt As Variant, HectoSp As Variant, LitresSt As Variant     Dim LitresSp As Variant, Ans As Variant, Title As String, WrAns As String     Dim DutySt As Variant, DutySp As Variant, CartonSt As Variant, CartonSp As Variant     Dim BottleSt As Variant, BottleSp As Variant     Title = "Customs Estimate"     WrAns = "You MUST enter a number!!"     'Sets up Input Box for number of cartons still     NumSt = InputBox("Enter number of Cartons of STILL wine", "Customs Estimate")         'If no number is entered or Cancel clicked then restarts sub         If IsNumeric(NumSt) Then                 NumSt = Int(NumSt)         Else:                 MsgBox WrAns             Exit Sub         End If         'Sets up Input Box for number of cartons sparkling     NumSp = InputBox("Enter number of Cartons of SPARKLING wine", "Customs Estimate")             'If no number is entered or Cancel clicked then restarts sub         If IsNumeric(NumSp) Then                 NumSp = Int(NumSp)         Else:                 MsgBox WrAns             Exit Sub         End If          'Converts NumSt & NumSp into Litres & Hectolitres             BottlesSt = NumSt * 6             BottlesSp = NumSp * 6             LitresSt = NumSt * 4.5             LitresSp = NumSp * 4.5             HectoSt = LitresSt / 100             HectoSp = LitresSp / 100         'Multiplies cartons by duty rate      oSheet = ThisComponent.getSheets.getByName("Rates")      dCellContent = oSheet.getCellRangeByName("A4").getValue      TotSt = BottlesSt * dCellContent      oSheet = ThisComponent.getSheets.getByName("Rates")      dCellContent = oSheet.getCellRangeByName("A6").getValue      TotSp = BottlesSp * dCellContent    Dim oCellRange As Object  Dim oCell As Object           Ans = MsgBox("Duty Still = £" & TotSt & vbNewLine _             & "Duty Sparkling = £" & TotSp & vbNewLine & vbNewLine _             & "Duty Total = £" & TotSt + TotSp & vbNewLine & vbNewLine _             & "               Continue?", 4, Title)     If Ans = 6 Then   ' answer = yes        oCellRange = oSheet.getCellRangeByName("E15:I16")        oCell = oCellRange.getCellByPosition(0,0) 'E15        oCell.setValue(HectoSt)        oCell = oCellRange.getCellByPosition(0,1) 'E16        oCell.setValue(HectoSp)        oCell = oCellRange.getCellByPosition(2,0) 'G15        oCell.setValue(TotSt)        oCell = oCellRange.getCellByPosition(2,1) 'G16        oCell.setValue(TotSp)        oCell = oCellRange.getCellByPosition(3,0) 'H15        oCell.setValue(NumSt)        oCell = oCellRange.getCellByPosition(3,1) 'H16        oCell.setValue(NumSp)        oCell = oCellRange.getCellByPosition(4,0) 'I15        oCell.setValue(BottlesSt)        oCell = oCellRange.getCellByPosition(4,1) 'I16        oCell.setValue(BottlesSp)     End If          End Sub                'Displays duty in a message box '    Ans = MsgBox("Duty Still = £" & TotSt & vbNewLine _ '            & "Duty Sparkling = £" & TotSp & vbNewLine & vbNewLine _ '            & "Duty Total = £" & TotSt + TotSp & vbNewLine & vbNewLine _ '            & "               Continue?", vbYesNo, Title) '        If Ans = vbYes Then                 'Displays Information in cell range E15:I16 if Yes box clicked '            Worksheets("Rates").Range("E15").Value = HectoSt '            Worksheets("Rates").Range("E16").Value = HectoSp '            Worksheets("Rates").Range("H15").Value = NumSt '            Worksheets("Rates").Range("H16").Value = NumSp '            Worksheets("Rates").Range("G15").Value = TotSt '            Worksheets("Rates").Range("G16").Value = TotSp '            Worksheets("Rates").Range("I15").Value = BottlesSt '            Worksheets("Rates").Range("I16").Value = BottlesSp '        End If      'Restarts if No box clicked '        If Ans = vbNo Then '            Exit Sub '        End If ' End Sub
eekfonky23
Power User

Joined: 18 May 2007
Posts: 55
Location: Scotland

 Posted: Tue Jul 31, 2007 3:40 pm    Post subject: That worked a treat! Can you please explain why it worked on my ubuntu OO 2.2.1 and not the Windows OO 2.2.1 version? I thought it was cross platform and would work either way. I'm new to this and keen to learn (good programming practices too) Thanks for your help
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 HoursGoto page 1, 2  Next Page 1 of 2

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum