OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

copy a working macro to another workbook then macro fails
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

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

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
Back to top
View user's profile Send private message Send e-mail MSN Messenger
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Thu Jul 19, 2007 12:03 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Thu Jul 19, 2007 12:04 pm    Post subject: Reply with quote

Maybe you can explain what you mean by "copying across workbooks."
Back to top
View user's profile Send private message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Wed Jul 25, 2007 7:34 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message Send e-mail MSN Messenger
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Jul 25, 2007 7:49 am    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Wed Jul 25, 2007 8:19 am    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Mark B
Super User
Super User


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

PostPosted: Wed Jul 25, 2007 10:44 am    Post subject: Reply with quote

Are you sure that the macro is embedded in the spreadsheet and not just in your own 'My Macros'?

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Wed Jul 25, 2007 10:49 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail MSN Messenger
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Wed Jul 25, 2007 11:30 am    Post subject: Reply with quote

you could try posting the actual file and have us look at it.
Back to top
View user's profile Send private message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Wed Jul 25, 2007 3:43 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Mark B
Super User
Super User


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

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

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

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

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?
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Thu Jul 26, 2007 8:17 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
swingkyd
OOo Advocate
OOo Advocate


Joined: 15 Sep 2004
Posts: 479

PostPosted: Thu Jul 26, 2007 9:38 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
eekfonky23
Power User
Power User


Joined: 18 May 2007
Posts: 55
Location: Scotland

PostPosted: Tue Jul 31, 2007 3:40 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail MSN Messenger
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group