[Solved] Solving the Sudoku Puzzle using OO BASIC Calc macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by Villeroy »

If I could, I would write a sudoku solver in any language Java, JavaScript, Python, C++ but NOT StarBasic and then wrap it into a Calc add-in with one array function that works like this:

Code: Select all

=SUDOKU(A1:I9) {Ctrl+Shif+Enter]
The array function would return the 9x9 solution for the 9x9 input.

There are plenty of free implementations of sudoku solvers in all programming languages. The most difficult part (well, for me at least) would be the compilation of a working Calc add-in. This is one of the many things I gave up trying some time ago.

The exact same solver code in another type of distribution package could be part of a mobile app, desktop application, interactive web page etc.
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
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Villeroy wrote:If I could, I would write a sudoku solver in any language Java, JavaScript, Python, C++ but NOT StarBasic and then wrap it into a Calc add-in with one array function that works like this:

Code: Select all

=SUDOKU(A1:I9) {Ctrl+Shif+Enter]
The array function would return the 9x9 solution for the 9x9 input.

There are plenty of free implementations of sudoku solvers in all programming languages. The most difficult part (well, for me at least) would be the compilation of a working Calc add-in. This is one of the many things I gave up trying some time ago.

The exact same solver code in another type of distribution package could be part of a mobile app, desktop application, interactive web page etc.
Hi. Villeroy.
Thanks for your response.
As I was quite new in using StarBasic, I am not familiar with the array function. Although I read something about this in Microsoft Excel a long time ago, I didn't really have much practice using that either.
I have yet to learn how to make add-in for Calc.
In my application, I didn't make use of any Calc worksheet functions. The only contact with worksheet was to load the cell values into an array and later to insert the solved result from the array back to another worksheet for display.

The whole solution was done in a few arrays, with operations including deleting of digits, hunting for hidden single values all done by the string functions REPLACE, LEN, MID, LEFT, RIGHT, and string CONCATENATION. The other functions include the array function of adding an item to an array, and removing an item from the array being the most critical.

When I first asked whether I could use the STARBASIC CALC macros to solve the Sudoku puzzles I had my tongue in my cheeks.
But I as experimented on the solution turned out to be surprisingly simple.

As programming is a participation game and not so much a spectator game, I can share the most critical codes with you without spoiling your fun of doing it yourself: (I noted that not many people downloaded my earlier partial solutions posted. It was said that it is most boring to read someone else codes)

To delete the unwanted digits from 123456789, use string function: REPLACE(Str, Cstr(n),"")
To hunt for the hidden single digit in a row, use string function: LEN(rowStr) - LEN(REPLACE(rowStr, Cstr(n),"")) = 1

With these two functions, most of the simple Sudoku puzzles can be solved, completely, in seconds.

Hi. Everyone.
In this application, I have been using some arrays of 1 dimension with the largest array being myArr(80).
I am now thinking of using some multidimensional arrays to rewrite the application.
In my testing /design stage, I had tried a 3 dimensional array, arr2(80,80,80), and it seemed to work.
However, when I tried a 4 dimensional array, arr3(80,80,80,80),
I encountered a fatal error: Bad allocation
and the OpenOffice Calc workbook was forced to close.
I tried to search for an answer in the forum but couldn't find the right answer.
Is a 3 dimensional array the limit I can use? Or it is something else that make this to break down?
Your advice is much appreciated.
Openoffice 4.15
Windows 10
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by JeJe »

https://bz.apache.org/ooo/show_bug.cgi?id=125006

dim arr3(80,80,80,80) works fine on my machine but 43046721 variants is a lot of memory.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by Villeroy »

I only tried to outline how you may use the same algorithm for multiple end user applications for all kinds of devices including mobile phones. You must not use StarBasic. It is an extinct dinosaur from the 90ies which is almost useless outside the context of some office suite (MS Office and this one). Programming Visual Basic is limited to Windows platforms. Even if you find some Basic interpreter for your mobile device, it would not interprete your StarBasic code which calls lots of functions that are specific to your office suite. Nobody programs Basic for mobile devices. The language is outdated. Modern scripting languages are better in every respect. Basic is not even easy to learn.

----------------

https://wiki.openoffice.org/wiki/Docume ... _functions
=TRANSPOSE(A1:I9) [Ctrl+Shift+ENter] returns the transposed matrix (rows and columns swapped).

An array reference:
=A1:I9 [Ctrl+Shift+Enter] returns the array of 81 cells as one formula result.

A regular function used in array context:
=INDEX(A1:X99 ; 13 ; 0) [Ctrl+Shift+Enter] returns 25 values from A13:X13 as one result.
=MAX(LENGTH(A1:A99)) [Ctrl+Shift+Enter] returns only one value from the outer MAX function but the inner LENGTH in array context returns the 99 text lengths from A1:A99.

A sudoku board is a limited array, typically 9x9 values and if we insist in using a spreadsheet as user interface, then we could choose a spreadsheet way of passing arrays in and out. A SIUDOKU function would work like the built-in TRANSPOSE function which takes an array and returns an array.
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
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by JeJe »

Villeroy - Visual Basic isn't limited to Windows Platforms:

https://www.mono-project.com/docs/about ... sualbasic/

There's also Mobile Basic and B4X

http://www.mobilebasic.com/

https://www.b4x.com/
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Hi. Villeroy.
Thanks for taking time to response.
Postby Villeroy » Sat Nov 03, 2018 10:49 pm
...
Nobody programs Basic for mobile devices. The language is outdated. Modern scripting languages are better in every respect. Basic is not even easy to learn.

....

https://wiki.openoffice.org/wiki/Docume ... _functions
I agree 100% with you that Basic is not even easy to learn.
Merely looking at those array functions from the site had me frozen up.

Your subsequent elaborations have hit the nail on the head, with a proper [hammer].

I have sort of done my depth-first search in programming in StarBasic, but hit a snag or two.

It is about time to do the [backtracking].

Perhaps I should seriously consider looking at [Javascript], or [Python] as someone has suggested.
Last edited by OldStd on Sun Nov 04, 2018 10:38 am, edited 1 time in total.
Openoffice 4.15
Windows 10
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Hi Jeje.
Thanks for taking time to response.

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m
Postby JeJe » Sat Nov 03, 2018 10:24 pm

https://bz.apache.org/ooo/show_bug.cgi?id=125006

dim arr3(80,80,80,80) works fine on my machine but 43046721 variants is a lot of memory.
It looks like I need a more powerful PC?
Thanks for the links.

I checked out the links. Some of them actually led me to new courses to take in order to make mobile apps.

I am not sure whether I want to spend some money to register for these courses as I am still with a hobbyist mind set.

I think I'll start looking at Javascript and see what I can do with it. I choose Javascript over Python at present because I am more familiar with procedural programming.

Perhaps I can develop a web application displaying and playing Sudoku game in a web page first and later think about how to make it accessible by a hand-phone instead of making an app that can be downloaded and played in a hand-phone?

Currently, I am brushing up my elementary knowledge in Javascript language by reading online
"The Modern Javascript Tutorial"
https://javascript.info/
Openoffice 4.15
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by Villeroy »

Array functions have nothing to do with macro programming. They are regular sheet functions for the spreadsheet user. Additionally you can use most of the regular functions in the context of an array formula which allows us to do some spreadsheet tricks more elegantly.

I am relatively sure that Sudoku is a problem that can be solved on a spreadsheet without macros. That's why I posted the sudoku1.ods a few days ago. It does not contain a single line of macro code and solves simple sudokus elegantly. I believe, it should be possible to expand this solution so it solves complex 9x9 sudokus without macro code, however I'm not sure about that.

The attachment has some trivial array formulas on Sheet1 and a mock up of a user-defined SUDOKU array function on Sheet2. The function always returns the same array, no matter what you enter in the referenced range.
Attachments
sudoku.ods
Teaching array functions
(17.86 KiB) Downloaded 233 times
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
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Incomplete solution for the more difficult sample
Incomplete solution for the more difficult sample
Hi. Villerory.
Thank you for your attachment of the mock-up Sudoku solution, Sudoku.ods.
First, I noted that this is only a mock-up, and not a solution.
Next, how do you get/make the array formula {=SUDOKU(A4:I12)}?

Intuitively, I always thought that to solve the Sudoku, we have to find the relationship between the empty cell to the row, or column, or 3x3 block that contains the cell. Is it possible to formulate a custom array formula to do just that?

In my solution, I employed 2 pencil and paper operations:
  • 1. Delete all digits in the row, column, 3x3 block from 123456789 and keep the remaining digits in the cell being processed.
    2. Search for a hidden single digits in the [remaining digits] in all the cells in a row, column, or 3x3 block. If found, update the content of the cell.
Are we able to employ some STRING FUNCTIONS or built-in cell-formulae inside the ARRAY FORMULA?

To repeat Step 1 and Step 2 above, I can use a LOOP in the macro to achieve that. If we do it manually on a worksheet, I guess we have to execute the array formula repeatedly down the worksheet, and manually inspect the result to know when to stop, where repeating the 2 steps won't yield any more [clue], that is a single digit in a cell.

Or, do you have any known array formula combined with any built-in cell formulae that can do the similar things for Step 1 and Step 2?

The learning curve for the above is simply too steep for the time being.

I am taking a closer look at your earlier attachment Sudoku1.ods.

I now isolate the cell-formulae used by looking them up in Google search:
DEC2BIN, RIGHT, SUBSTITUTE, MID, SEARCH.

Of these, 3 are new to me. Looking them up in the OO Documentation/howtos/functions yield the following.
(Now the codes don't look so intimidating.)

DEC2BIN
Converts a decimal number to binary.

This function is only available if the Analysis AddIn is installed.

Syntax:
DEC2BIN(number; numdigits)

returns a binary number as text, given the decimal number, which must be between -512 and 511 inclusive, and may be text or a number.
The output is a binary number with up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal).
numdigits is an optional number specifying the number of digits to return.
Example:
DEC2BIN(9)

returns 1001 as text.


SUBSTITUTE
Substitutes new text for old text in a text string.

Syntax:
SUBSTITUTE(originaltext; oldtext; newtext; which)

In originaltext, removes oldtext, inserts newtext in its place, and returns the result. oldtext and newtext can have different lengths.
which (optional) is a number which specifies which occurrence of oldtext to replace (counting from the left). If omitted, all occurrences are replaced.
Example:
SUBSTITUTE("castle"; "stl"; "v")

returns cave.

SEARCH
Returns the position of a string of text within another string.
Syntax:
SEARCH(findtext; texttosearch; startposition)
returns the character position of the first occurrence of findtext within texttosearch.
startposition (optional) is the position from which the search starts.
The search is not case-sensitive.
The search will use regular expressions, if they are enabled (Tools - Options - OpenOffice.org Calc - Calculate).
A failed search gives the #VALUE! error.
In Tools - Options - OpenOffice.org Calc - Calculate the setting for Search criteria = and <>must apply to whole cells has no effect.
Example:
SEARCH("yo"; "Yoyo")
returns 1. The search is case-insensitive.
SEARCH("cho"; "choochoo"; 2)
returns 5.
SEARCH("t.n"; "often")
returns 3, if regular expressions are enabled. The "." stands for any single character in a regular expression, so "t.n" matches "ten".
SEARCH("xyz","abcdef",1)
returns #VALUE!.
NOTE: This is an error condition, which must be 'handled' if used as the argument to another function.

I tested the solution with 2 samples of Sudoku puzzles. For the simple sample it worked beautifully. The solution fell short for the more difficult puzzle where a choice between 2 digits, and applying depth-first search and backtracking is required and repeated for a couple of cycles.

So that is the limitation of cell-formula approach.

Please see the attachment for the results of the 2 trials.
Openoffice 4.15
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by Villeroy »

The attached extension is more than a mock up. It contains the array function I had in mind.
I found the solver algorythm on Peter Norvig's web site. It is very straight forward code in procedural style taking a string of 81 characters as input argument. The input string consists of either digits and or points. Zero or point represent a gap in the puzzle. The solver routine returns a dictionary ("hash" in other languages) with rows A,B,C,...,I and columns 1,2,3,...,9 which is the other way round than the typical spreadsheet grid. "C1" refers to the value in the first column, row #3.
In Python I only had to convert the nested data array of 81 spreadsheet values to a string of 81 characters and convert the output dictionary into a nested data array.
The code is wrapped in one Python module because I could not find a way to import modules in the context of a Calc extension.
The well known PyTextfunctions add-in served me as a template to write all the configurations and interface declaration. From the interface declaration (*.idl) one as to compile binary stuff (*.urd) and merge the interface with an office suite which finally gives an *.rdb file to be included in the add-in config. I included the idl and urd in subdir ./sources for reference.
I added a German localization to the English one. In the function wizard PYSUDOKU is listed in the group of array functions. A German GUI shows the German descriptions of function and argument.
The resulting package is compatible with LibreOffice and OpenOffice 2.4. or later.

On my Linux sytem I used LibreOffice 6.1 with its SDK for the compilation steps like this:

Code: Select all

~/ooopy/Sudoku/sources$ /opt/libreoffice6.1/sdk/bin/idlc XPySudoku.idl
~/ooopy/Sudoku/sources$ /opt/libreoffice6.1/program/regmerge ../XPySudoku.rdb org.openoffice.sheet.addin.XPySudoku XPySudoku.urd
[No, I don't really understand any of these steps. After reading the SDK documentation it turns out to be doable though.]

Installation:
Open the package file PySudoku.oxt with your office suite or use application menu Tools>Extensions...
Hit the [Add] button to install the package file.
Restart the office suite.

Usage:
Enter your Sudoku puzzle into a 9x9 cell range. Any blank or zero value or any kind of text value will be treated as missing number.
Enter =PYSUDOKU(A1:I9) as an array function with Ctrl+Shift+Enter instead of the plain Enter key. A1:I9 is just an example. It could be any 9x9 cell range.
An array function is a spreadsheet function that returns more than one value at once (9x9 values in this case). You can copy tiles of array functions along tiles of puzzles since relative referencing works just as usual.
Attachments
PySudoku.oxt
Calc add-in with function PYSUDOKU. Based on brilliant code by Peter Norvig.
(9.08 KiB) Downloaded 311 times
Last edited by Villeroy on Mon Nov 12, 2018 10:48 pm, edited 1 time in total.
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
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Hi. Villeroy.
Thanks for the link and the explanation.
I have tried to open the downloaded file, but was greeted with “to install the extension.”
As I am new to the idea of installing the extension, I got stuck somewhere and didn’t succeed in opening the file.
I am also unfamiliar with the use of array function for the worksheet.
So, the whole exercise is rather intimidating, for the time being.

As I am learning the basics of JavaScript, what little I had read on Python kept popping up in my mind.

I am thinking perhaps I can make good use of the [Set], that can only take unique elements, [Dictionary] that uses [key] to quickly identify an element without looping through the whole sequence, and
  • where I can [add], [remove], and [update] an element more easily than using [Array] in AOO as before. Perhaps I might as well switch to quickly learn up Python in order to rewrite my routines developed in AOO earlier.

    After that I may need to learn how to convert the Python codes into an AOO extension or LibreOffice extension.

    I have installed LibreOffice 6.0.7 without installing the SDK. Maybe that was why I couldn’t install the extension you attached. I may have to install LibreOffice 6.1.3 and SDK to do the job.

    It may be difficult to decipher someone else s’ codes when no explanations were given, or even if explanation was given but beyond my level of comprehension. Explanation in Germans definitely won’t help.

    It may be easier for me to work out a solution based on what I had done in another programming language, AOO, but only changing the syntax to the new language, Python.

    As I pointed out in another post, I am now trying to use base-10 numbers 1, 10, to 100000 to represent 1, 2, to 9 in my new approach and the goodies from Python may make it a bit easier to program, even if it may not be any fester or more elegant.

    I have yet to find out for myself.

    Any instructions on converting a program written in Python to an extension in LibreOffice would help a lot.
Openoffice 4.15
Windows 10
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Villeroy wrote:
....
The resulting package is compatible with LibreOffice and OpenOffice 2.4. or later.
.....
On my Linux sytem I used LibreOffice 6.1 with its SDK for the compilation steps like this:

Installation:
Open the package file PySudoku.oxt with your office suite or use application menu Tools>Extensions...
Hit the [Add] button to install the package file.
Restart the office suite.

Usage:
Enter your Sudoku puzzle into a 9x9 cell range. Any blank or zero value or any kind of text value will be treated as missing number.
Enter =PYSUDOKU(A1:I9) as an array function with Ctrl+Shift+Enter instead of the plain Enter key. A1:I9 is just an example. It could be any 9x9 cell range.
An array function is a spreadsheet function that returns more than one value at once (9x9 values in this case). You can copy tiles of array functions along tiles of puzzles since relative referencing works just as usual.
Hi. Villerory.

I installed the extension as instructed and tried to use the extension as instructed.
It didn't work on my PC with Windows 10. LibreOffice 6.1 and SDK

A screenshot of the error message is attached.

I don't know what went wrong and what to do next with the extension.
Err 504
Err 504
Openoffice 4.15
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by Villeroy »

The error number 504 is misleading. It should be 522 (circular reference). You must not reference the same range for input and output.
Attachments
pysudoku.ods
Example sheet using the PYSODUKU add-in function
(23.06 KiB) Downloaded 233 times
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
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Greetings. Villerory.

I uninstalled and reinstalled the PySudoku extension and downloaded your sample workbook.

It took me quite a while to understand what you mean by having the sample and solution in different locations.

Once that was understood, I was able to show that the extension also works in my PC. It is so fast, too.

Using the same extension, I was able to show 2 different solutions to the sample I posted first in my earlier post.

After this, I think I would try to develop a method to solve the Sudoku puzzles, on my own, from first principles learning Python from scratch rather than trying to understand a very advanced model developed by a very experienced Python user, as I can't understand his logic and the intrigue functionalities of the Python objects he is using that is beyond and beyond me.

One of these days when I am well versed in Python programming, I may be able to write a solution as elegant, but not now.

You are correct to say that I don't understand procedural programming because I have subsequently read that Javascript is not for procedural programming and I declared in an earlier post that I wanted to learn Javascript first because I am more familiar with procedural programming.

Thanks for the link, and the patience to explain to me to use the array formula to work the extension.
Openoffice 4.15
Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by Villeroy »

First things first. No matter which language, you have to learn how to call procedures with arguments and how to handle the return values. Spreadsheets are a good start because spreadsheets provide a simplified and visual programming language with only 3 types number, text and error. You have to learn some langauge before you can solve anything, let alone Sudoku.

If you can't call routines with arguments, you can not even use a spreadsheet adequately. This is the same principle in spreadsheets, SQL (formula langauge of databases), Basic, Python, Java, JavaScript, Python, Perl, Php, C, C++ and most of the others too.

ReturnValue = Routine(input_to_process)
In programming languages the return value is represented by some variable name. In strictly object oriented languages you call it like this:
ReturnObject = Object.Routine(input_to_process) but the principle is the same.
Python is an object oriented language. Nevertheless it lets you freely program in completely procedural ways like Peter Norvig did for his Sudoku solver. I had to add some object oriented code in order to add a callable object to this office suite. My object introduces itself to the office suite providing a spreadsheet function PYSUDOKU and when this function is called with an appropriate input argument (the puzzle as 9x9 array of data), it calls the Peter's procedural stuff, namely a routine called solve(input_data) and from the return value of the solve routine my function produces another 9x9 array of output data and returns that array to the calling spreadsheet application.

In a spreadsheet the return value of a function call is directly displayed in the cell where the routine has been entered. The return values of array functions may spread over multiple cells.
=SUM(A1:I9) processes data from A1:I9 and returns the sum of all numbers in A1:I9 ignoring any text as a single value (no array function).
=PYSUDO(A1:I9) processes data from A1:A9 and the return values are spread across another 9x9 cell range. This is why you have to use it as an array function with Ctr+Shift+ENter.
=VLOOKUP(23;A1:I9;3;0) looks up 23 in the first column of A1:I9 (A1:A9) and returns the corresponding value from the 3rd column which is C1:C9 in this example.
If you enter any of these functions within A1:I9, you get a recursion error because the result of SUM(A1:I9) depends on the value of the cell itself which is not resolvable. Unfortunately, my add-in function returns a wrong error value but the correct reason is that you can not solve a sudoku puzzle out of nothing. That function would be called like this: =PYSUDOKU() without any input argument. This may generate randomly solved sudoku arrays or unsolved sudoku puzzles or whatever without taking any argument. But that's not what we were after.

VLOOKUP takes 4 positional arguments with different meanings. Each argument means something different at its position. Same as in programming languages. But in programming languages you define your own routines with input arguments and return values all the time as soon as you left behind the first chapters of your beginners book. But even then you are miles away from solving Sudoku.
Regarding the subject of this topic "Solving the Sudoku Puzzle using OO BASIC Calc", you have to learn a lot of elementary things before you can do that. It could be done with spreadsheet language alone. It could be done with Basic. It can be done with other languages reading and writing to spreadsheets and of course it can be done with dozends of other languages completely unrelated to spreadsheets. Peter Norvig certainly did not have any spreadsheet in mind when he wrote that elegant solver. Luckily he did it in Python so I could add some object oriented glue code and configuration for a new Calc add-in. In case you don't know yet: the add-in package file is a zip archive. You can extract its files with any zip tool. Same with ODF documents (odt, ods, odp etc).

P.S: You know what? I don't know how Peter Norvig solves Sudoku in Python either. I did not have a closer look at the code. I only analysed which routine takes which types of argument and gives which types of data in return.
Calc calls my PYSUDOKU(data_array), my PYSUDOKU converts the data array into a string of digits before calling solve(digits) by Peter Norvig, solve returns a Python dictionary to my PYSUDOKU which converts this Python dictionary into another data array which is returned back to Calc. I only had to understand that Peter's code expects a string of digits to get and passes a Python dictionary in return whereas Calc passes over a data array and expects to get the same type in return.
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
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Greetings. Villerory.
Thank you for taking time to write out such a comprehensive refresher course in calling routines with augments, and how to compile the Calc Add-in from Peter's Python codes.
As I am going to learn Python and developing a solution in Python for Sudoku from scratch, I'll learn from you later how to compile a Calc add-in from my Python codes.
I did have some elementary knowledge and practice in calling routines with arguments.
In case you missed it, I had posted my Macro [main] in an earlier post. That is the actual routine calling the other routines for the complete solution to Sudoku puzzles, using an array as in input parameter. In some of the routines, I have used more than one argument.

Code: Select all

REM  *****  BASIC  *****
option explicit

global arrRcb(2) as integer
global c1, c2, d, e, l, u as integer
global i, j, k, r, c, b, a, n as integer
global Cell As Object  
global Doc As Object
global Sheet As Object
global farr(8) as string
global myArr(80) as string
global s, sr, sc, sb as string
global Arr2(1) as variant
global Arr3(1) as variant
global oArr() as variant
global outarr0(80), outarr1(80), outarr2(80), outarr3(80) as string
global outarr23(80), outarr34(80), outarr45(80) as string

Sub Main
	dim inArr(80) as string
	rem Clear oArr of old arrays stored
	u = UBound(oArr)
	for i = u to 0 step -1
		RemoveItemFromyArr(oArr, i)
	next i
	rem msgbox u
	outArr0 = fn0sht2Array
	outArr1 = fn1fillBlanks(outArr0)
	outArr2 = fn2delSngl (outArr1)
	outArr3 = fn3getUniques(outArr2)
	inarr = outarr3
	for k = 1 to 10
		outArr23 = fn2n3loops(inarr)
		if not fnsolved(outarr23) then
			outArr34 = fn3n4loops(outArr23)
		end if
		if not fnsolved(outarr34) then
			outArr45 = fn4n5loops(outArr23)
		end if
		inarr = outArr45
		if fnsolved (outArr23) then
			calldisplay(outArr23)
			msgbox "solved!" & "   k= " & k
		exit for
		end if
	next k
End Sub
When I first embarked on the project, there were a few nays. I kept on because I was inspired.
In my first project in learning to write BASIC codes using API, under the guidance of Zizi64, I was wondering how to count a certain character/letter in a string, for example How many [s] are there in [assessing]? My search in Google gave the answer in an example,
LEN (“assessing”) - LEN(REPLACE(“assessing”,”s”,””) = 9 – 5 = 4.
This gave me the idea of applying these 2 STRING FUNCTIONS, LEN and REPLACE in the the first strategy in solving the Sudoku.
For every cell in the Sudoku board, find the numbers already present in the associated row, column, and 3x3 block, and if we replace the numbers one-by-one from the string “123456789”, then what is left are the numbers that can be tried as a solution. If the remaining number is a single digit then we have found the solution for that cell. This was translated into my function, fn2delsngl, as appears in the macro [main].

The methodology had been explained over and over in the previous posts, and the codes for the early steps for finding the [naked singles in cells], and the [hidden singles in row, or column, or 3x3 box] were posted in earlier posts as Sudoku_001.ods, Sudoku_002.ods, and Sudoku_003,ods. I see that not so many people downloaded the codes, even though there were over 2700 views of this topic in the forum. Programmers are proud people who prefer to write their own codes than reading someone else's codes.

Now that I had found a simple solution to the Sudoku using only some simple STRING FUNCTIONS, 1-DIMENSIONAL ARRAYS, and the FOR loops, with the realization that I may be able to represent 1 to 9 with 10 **(n-1) for n in Range(1,10), as in Python, and looking at the properties of SETS, DICTIONARIES, TUPLES, and LISTS I believe I can translate the same ideas I had used in earlier to solve the Sudoku again.

Perhaps I should create a new topic

“Solving Sudoku with Python and Calc Add-in”
Openoffice 4.15
Windows 10
OldStd
Posts: 116
Joined: Tue Sep 11, 2018 8:46 pm

Re: [Solved] Solving the Sudoku Puzzle using OO BASIC Calc m

Post by OldStd »

Hi. Everyone.

I have just signed up for a course in Python in Udemy, under the [$9.99 Black Friday Sale].
This offer will expire in 2 days. It is a 95% discount.
I think it is worth it, if it helps me to solve the Sudoku a bit more easily. :D
Openoffice 4.15
Windows 10
Post Reply