Excel Macro not running on Libreoffice Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
microhunt
Posts: 6
Joined: Tue Apr 26, 2022 1:05 pm

Excel Macro not running on Libreoffice Calc

Post by microhunt »

I am using this macro on excel which runs no problems. However in Libreoffice it fails. and I get Basic Syntax error. Can anyone offer advice why it will not work?

Code: Select all

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Option Explicit

''/////////////////////////////////////////////////////////////////
''//  3rd August 2010 //
''// Created by Vog
''/////////////////////////////////////////////////////////////////
#If VBA7 Then
    Public Declare PtrSafe Function GetActiveWindow Lib "user32" () As Long

    Public Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
                                                                                    ByVal hWnd1 As LongPtr, _
                                                                                    ByVal hWnd2 As LongPtr, _
                                                                                    ByVal lpsz1 As String, _
                                                                                    ByVal lpsz2 As String) _
                                                                                    As LongPtr

    Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As LongPtr, _
                                                                                   ByVal wMsg As Long, _
                                                                                   ByVal wParam As LongPtr, _
                                                                                   lParam As Any) _
                                                                                   As LongPtr

    Public Declare PtrSafe Function SetTimer Lib "user32" ( _
                                                            ByVal hwnd As LongPtr, _
                                                            ByVal nIDEvent As LongPtr, _
                                                            ByVal uElapse As Long, _
                                                            ByVal lpTimerFunc As LongPtr) _
                                                            As LongPtr

    Public Declare PtrSafe Function KillTimer Lib "user32" ( _
                                                            ByVal hwnd As LongPtr, _
                                                            ByVal nIDEvent As LongPtr) _
                                                            As Long

    Public Declare PtrSafe Function GetForegroundWindow Lib "user32" () As LongPtr

#Else
    Public Declare Function GetActiveWindow Lib "user32" () As Long

    Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
                                                                            ByVal hWnd1 As Long, _
                                                                            ByVal hWnd2 As Long, _
                                                                            ByVal lpsz1 As String, _
                                                                            ByVal lpsz2 As String) _
                                                                            As Long

    Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, _
                                                                           ByVal wMsg As Long, _
                                                                           ByVal wParam As Long, _
                                                                           lParam As Any) _
                                                                           As Long

    Public Declare Function SetTimer Lib "user32" ( _
                                                    ByVal hwnd As Long, _
                                                    ByVal nIDEvent As Long, _
                                                    ByVal uElapse As Long, _
                                                    ByVal lpTimerFunc As Long) _
                                                    As Long

    Public Declare Function KillTimer Lib "user32" ( _
                                                    ByVal hwnd As Long, _
                                                    ByVal nIDEvent As Long) _
                                                    As Long

    Public Declare Function GetForegroundWindow Lib "user32" () As Long
#End If

Private Const EM_SETPASSWORDCHAR = &HCC

#If VBA7 Then
    Private Const nIDE                  As LongPtr = &H100
    Private hdlEditBox                  As LongPtr
    Private Fgrndhdl                    As LongPtr
#Else
    Private Const nIDE                  As Long = &H100
    Private hdlEditBox                  As Long
    Private Fgrndhdl                    As Long
#End If
#If VBA7 Then
    Public Function TimerFunc( _
        ByVal hwnd As LongPtr, _
        ByVal wMsg As Long, _
        ByVal nEvent As LongPtr, _
        ByVal nSecs As Long) As Long
    Dim hdlwndAct As LongPtr
#Else
    Public Function TimerFunc( _
        ByVal hwnd As Long, _
        ByVal wMsg As Long, _
        ByVal nEvent As Long, _
        ByVal nSecs As Long) As Long
      
        Dim hdlwndAct As Long
#End If
    
    '// Do we have a handle to the EditBox
    If hdlEditBox > 0 Then Exit Function
  
    '// Get the handle to the ActiveWindow
    hdlwndAct = GetActiveWindow()
  
    '// Get the Editbox handle
    hdlEditBox = FindWindowEx(hdlwndAct, 0, "Edit", "")
  
    '// Set the password character for the InputBox
    SendMessage hdlEditBox, EM_SETPASSWORDCHAR, Asc("*"), ByVal 0

End Function

Public Function InPutBoxPwd(fPrompt As String, _
    Optional fTitle As String, _
    Optional fDefault As String, _
    Optional fXpos As Long, _
    Optional fYpos As Long, _
    Optional fHelpfile As String, _
    Optional fContext As Long) As String
    
    Dim sInput As String
    
    '// Initialize
    hdlEditBox = 0
    Fgrndhdl = GetForegroundWindow
    '// Windows-Timer
    SetTimer Fgrndhdl, nIDE, 100, AddressOf TimerFunc
    
    '// Main InputBox
    If fXpos Then
        sInput = InputBox(fPrompt, fTitle, fDefault, fXpos, fYpos, fHelpfile, fContext)
    Else
        sInput = InputBox(fPrompt, fTitle, fDefault, , , fHelpfile, fContext)
    End If
    
    '//
    KillTimer Fgrndhdl, nIDE
    '// Pass result
    InPutBoxPwd = sInput
    

End Function

Sub Clearrange(sRange As String, sWorksheet As String)
    Worksheets(sWorksheet).Range(sRange).ClearContents
End Sub

Sub ClearHouse1(sWorksheet As String)
 ' if the layout of the sheet changes,
 ' add or edit the ranges here for the new layout

    Clearrange ("A6:A1077"), sWorksheet         'Date
    Clearrange ("B6:B1077"), sWorksheet         'Quantity
    Clearrange ("C6:C1077"), sWorksheet         'Name
    Clearrange ("D6:D1077"), sWorksheet         'Code
    Clearrange ("F6:F1077"), sWorksheet         'Serial Number
    Clearrange ("I6:I1077"), sWorksheet         'Delivery Cost
    Clearrange ("L6:L1077"), sWorksheet         'Invoice Number
    Clearrange ("O6:Q1077"), sWorksheet         'Ebay Selling Price, Postage Customer Paids & Postage I Paid
    
    
End Sub



Sub ClearHouserent1()
Const ok As String = "jib"
Dim pw As String
pw = InPutBoxPwd("Are you sure you want to clear this sheet") '<<<<<<<<<<<<<<<<<<<<<< Changed line
If pw <> ok Then
    MsgBox "Wrong password"
    Exit Sub
End If
Call ClearHouse1("Calculator")

End Sub
Version 7.3.2.2 LibreOffice / Ubuntu 22.04LTS
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Excel Macro not running on Libreoffice Calc

Post by JeJe »

The first error I get is a LongPtr declaration - which isn't a data type in LibreOffice

The second is the Function TimerFunc within another function - which isn't allowed...

Did you write this code yourself or is it code you've copied from somewhere else?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
microhunt
Posts: 6
Joined: Tue Apr 26, 2022 1:05 pm

Re: Excel Macro not running on Libreoffice Calc

Post by microhunt »

JeJe wrote:The first error I get is a LongPtr declaration - which isn't a data type in LibreOffice

The second is the Function TimerFunc within another function - which isn't allowed...

Did you write this code yourself or is it code you've copied from somewhere else?
This is code that VoG from MRexcel wrote for me in 2010. Is there anyway to covert this macro or is it a no go and totally not fixable?
Version 7.3.2.2 LibreOffice / Ubuntu 22.04LTS
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Excel Macro not running on Libreoffice Calc

Post by JeJe »

I can see AddressOf in there which won't work in LibreOffice either. Start from scratch in LibreOffice Basic might be best.

What's it do? Shows an input box and if the password is correct clears the sheet?
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
microhunt
Posts: 6
Joined: Tue Apr 26, 2022 1:05 pm

Re: Excel Macro not running on Libreoffice Calc

Post by microhunt »

JeJe wrote:I can see AddressOf in there which won't work in LibreOffice either. Start from scratch in LibreOffice Basic might be best.

What's it do? Shows an input box and if the password is correct clears the sheet?
Yes that is basically it. It clear a number of worksheets in the workbook. O do not know how to do this so I think I am stuck with Microsoft Excel in the meantime.

Thank you :mrgreen:
Version 7.3.2.2 LibreOffice / Ubuntu 22.04LTS
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Excel Macro not running on Libreoffice Calc

Post by JeJe »

Most of the code looks to be making an input box have password characters. Try this... having a plain input box without password characters. Apart from that does this work?

Code: Select all

    Option VBASupport 1
    Option Explicit

    Sub ClearHouserent1()
    Const ok As String = "jib"
    Dim pw As String
    pw = InPutBox("Are you sure you want to clear this sheet") '<<<<<<<<<<<<<<<<<<<<<< Changed line

    If pw <> ok Then
    MsgBox "Wrong password"
    Exit Sub
    End If
    
    Call ClearHouse1("Calculator")

    End Sub

    Sub Clearrange(sRange As String, sWorksheet As String)
    Worksheets(sWorksheet).Range(sRange).ClearContents
    End Sub

    Sub ClearHouse1(sWorksheet As String)
    ' if the layout of the sheet changes,
    ' add or edit the ranges here for the new layout

    Clearrange ("A6:A1077"), sWorksheet 'Date
    Clearrange ("B6:B1077"), sWorksheet 'Quantity
    Clearrange ("C6:C1077"), sWorksheet 'Name
    Clearrange ("D6:D1077"), sWorksheet 'Code
    Clearrange ("F6:F1077"), sWorksheet 'Serial Number
    Clearrange ("I6:I1077"), sWorksheet 'Delivery Cost
    Clearrange ("L6:L1077"), sWorksheet 'Invoice Number
    Clearrange ("O6:Q1077"), sWorksheet 'Ebay Selling Price, Postage Customer Paids & Postage I Paid

    End Sub
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Excel Macro not running on Libreoffice Calc

Post by JeJe »

The attached document is the same except it replaces the input box with a dialog which shows the * instead of the password when its typed.

Edit: fixed two okay buttons... bit too hasty there!
Attachments
password clear range.ods
(12.4 KiB) Downloaded 90 times
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
microhunt
Posts: 6
Joined: Tue Apr 26, 2022 1:05 pm

Re: Excel Macro not running on Libreoffice Calc

Post by microhunt »

JeJe wrote:The attached document is the same except it replaces the input box with a dialog which shows the * instead of the password when its typed.

Edit: fixed two okay buttons... bit too hasty there!
Thank you very much. How do you add this. Do I import Macro?
Version 7.3.2.2 LibreOffice / Ubuntu 22.04LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel Macro not running on Libreoffice Calc

Post by Villeroy »

All you need is a document template with all data removed. In order to fill a new document with a new data set all you have to do is to open the template.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
microhunt
Posts: 6
Joined: Tue Apr 26, 2022 1:05 pm

Re: Excel Macro not running on Libreoffice Calc

Post by microhunt »

Villeroy wrote:All you need is a document template with all data removed. In order to fill a new document with a new data set all you have to do is to open the template.
Thank you I ran the new code and got this error when I ra n the macro

Image
Version 7.3.2.2 LibreOffice / Ubuntu 22.04LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel Macro not running on Libreoffice Calc

Post by Villeroy »

A template solution does not require a single line of Basic code and works with any office program.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
microhunt
Posts: 6
Joined: Tue Apr 26, 2022 1:05 pm

Re: Excel Macro not running on Libreoffice Calc

Post by microhunt »

Villeroy wrote:A template solution does not require a single line of Basic code and works with any office program.
I am doing something wrong? What do I need to do to make it work?

Thanks in advance
Version 7.3.2.2 LibreOffice / Ubuntu 22.04LTS
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Excel Macro not running on Libreoffice Calc

Post by JeJe »

You're meant to download the document I posted, open it in LibreOffice and press the "clear ranges" button. It just works on that document... or seems to as part of the spreadsheet range is cleared.

If you want it to work on another document you need to move the module with the code and the dialog to the other document. You can do this with the macro organizer.

"Moving or copying modules between documents, templates and the application."

https://help.libreoffice.org/6.2/en-US/ ... 30400.html

 Edit: Edit: another way is to export the dialog from my document - there's a toolbar button in the Basic IDE to do that. And then switch to the other document and click the import dialog button.
You can just copy and paste the module code into your other document. 
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Post Reply