Error: Sub Expected...

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Error: Sub Expected...

Post by cavaleri69 »

Howdy, I'm not having much luck in attempting to get this a Sub Module in an embedded macro to work in this document. This document (attached) was given to me by a professor for an Options & Futures MBA class, to try to visualize option pricing, look at the 'Greeks', etc.. I realize this was intended to be run on a Windows machine, but the code doesn't look too far off from VBA to try to get it working in OO. I'm currently running version 3.1, on Mac 0S 10.5.8.

The first problem, is that I opened the document as .xls, and resaved as .ods. since I hear that might help resolve the #NAME? errors that appear in the document. When I go to Tools->Macros->Organize Macros->OO Basic, and then click the Module 2 macro under OptionTradingWoorkbook2->Standard->Module2 (Edit) I get the following error when I go to try to compile the code:

BASIC Syntax Error
Expected: Sub

I attempted to verify the VBA code as OO Basic first by using an on-line converter, and there was no difference (verified by the unix diff command). I've never used VBA or macros in Excel, so I'm not sure what the problem here is..

Any pointers, suggestions, etc., on how to make this work would be very helpful, I have a quiz due Tuesday that depends on getting this working. If I can't get it to work here, the next option will be to try to get Parallels working on my mac, but I'd like to see if it's possible to get this working on OO first.
Attachments
OptionTradingWorkbook2.ods
(93.44 KiB) Downloaded 322 times
OpenOffice 3.1.0 on Mac 10.5.8
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error: Sub Expected...

Post by Zizi64 »

I found some bugs:

1.: The function calling in cells not works, because all of functions inserted only one SUBROUTINE. I deleted "Sub" and "End sub" directives.

2.: There is an function named "Gamma": GAMMA(parameter1) exists in Calc. I have rename your user function. For example: "GAMMA_1(par1; par....)"

3.: I deleted unused Basic modules

I do not know, the functions work properly or not, but they WORK NOW for me,
except this part: "Application.NormSDist(dOne(Under...."
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Re: Error: Sub Expected...

Post by cavaleri69 »

Tibor,

thank you so much for the quick reply. I've tried what you suggested so far, I deleted the Sub Module2, and End Sub lines. When I went to compile, it still gave me the same error, but when I commented out the top line: 'Attribute VBA_ModuleType=VBAModule it didn't give any errors. I also re-named the Gamma_1 function as you suggested, and this actually PRODUCES a value now! However, the CallOption function still just says #NAME? in the cells under the 'Basic' tab. Were you able to get this function to excecute with any values? If so, would you mind re-attaching your version for me to try?

Many many thanks!
OpenOffice 3.1.0 on Mac 10.5.8
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error: Sub Expected...

Post by Zizi64 »

The file size inreased (I deleted some modules???), I upload it zipped.
OptionTradingWorkbook3.ods.zip
(97.78 KiB) Downloaded 290 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error: Sub Expected...

Post by Zizi64 »

Code: Select all

Application.NormSDist...
What "Application", wich "NormSdist" function?

In Calc exists "NormSDist(number)" function, but calling a Cellfunction from Basic code is different than in Excel VBA.
see:
http://user.services.openoffice.org/en/ ... on#p157134

for example:

Code: Select all

public function RAND() as Double
       Dim svc As Object
   
    svc = createUnoService("com.sun.star.sheet.FunctionAccess")
    RAND = svc.callFunction("ZUFALLSZAHL",Array())

end function
Last edited by Zizi64 on Sun Nov 14, 2010 10:35 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Re: Error: Sub Expected...

Post by cavaleri69 »

Something weird is definitely happening, I successfully unzipped your file, but when I go to open it, OO just goes grey and gives me a small dialog box that beeps": BASIC Syntax Error: Sub Expected... If I click it close, it doesn't go away, it just locks up.. I have to use the activity monitor to actually force kill OO. and re-load. I tried it several times, same thing occurs. I wonder if I should try to upgrade OO to version 3.2? This is a very unexpected error!

I do appreciate all of your help with my problem there, thanks in advance.
OpenOffice 3.1.0 on Mac 10.5.8
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Re: Error: Sub Expected...

Post by cavaleri69 »

The NormSDist function I assume works like the Excel NORMSDIST() function, which returns a probability that the observed value of a standard normal random variable will be less than or equal to z. (A standard normal random variable has mean 0 and standard deviation 1, and also variance 1 because variance = standard deviation squared). I don't know how to modify this function if it needs to be to use a different version of the Random function... ?
OpenOffice 3.1.0 on Mac 10.5.8
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Re: Error: Sub Expected...

Post by cavaleri69 »

Well, I tried upgrading to 3.2.1 to see if that would change anything with the broken document dialog, and it still does the same thing (hangs upon opening).
OpenOffice 3.1.0 on Mac 10.5.8
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error: Sub Expected...

Post by Zizi64 »

I hope it will work (a lot of time was needed for a ?solution?):

I tried to modify the calling of the "Application.NormSDist()" function...
One of those calling:

VBA:

Code: Select all

 CallDelta = Application.NormSDist((Log(UnderlyingPrice / ExercisePrice) + Interest * Time) / (Volatility * Sqr(Time)) + 0.5 * Volatility * Sqr(Time))
OOo Basic:

Code: Select all

    svc = createUnoService("com.sun.star.sheet.FunctionAccess")
    a_Data() = Array((Log(UnderlyingPrice / ExercisePrice) + Interest * Time) / (Volatility * Sqr(Time)) + 0.5 * Volatility * Sqr(Time))
    My_NormSDist_1 = svc.callFunction("NormSDist", a_Data())
    CallDelta = My_NormSDist_1 
OptionTradingWorkbook4.ods.zip
(100.3 KiB) Downloaded 269 times
I don't know how to modify this function if it needs to be to use a different version of the Random function
(The example with Random() function points only: How to call the Calc cellfunctions from basic code...)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Re: Error: Sub Expected...

Post by cavaleri69 »

Tibor, I'm extremely grateful for all the time you've spent diagnosing my problem. I seem to be having a larger problem here, as I can't seem to open your documents, which cause my OO to crash upon loading. I get the message BASIC Syntax Error: Expected: Sub As soon as it opens, with no way to close the dialog, and I'm forced to kill the process.

Any ideas on what could be going wrong with that ?? Once I get past this, I'm eager to look over your work.

Perhaps I should post this more fundamental problem as a separate thread?
OpenOffice 3.1.0 on Mac 10.5.8
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error: Sub Expected...

Post by Zizi64 »

I downloaded my example file again (from this forum) to an another PC (WinXP, and OxygenOffice3.2.1 too), and I can unzip it , I can open it without any error message.
The all of functions seems work, I have not any error message in cells.

Are the results good? I do not know...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
cavaleri69
Posts: 7
Joined: Sun Nov 14, 2010 6:06 pm

Re: Error: Sub Expected...

Post by cavaleri69 »

Ok, I will try to see if I can attempt this on another machine, perhaps there is something going on w/ my version running on Mac OS 10.5.8
OpenOffice 3.1.0 on Mac 10.5.8
Post Reply