Can't Get Rid of Error 9 in Basic for Calc

[Robitron]

Hey y'all. Here's the situation. I have 30 people and 7 foods. I need each person to rank their favorite food from 1 to 7. This is all theoretical so I need to use a random number that does not duplicate to represent each person's rank preferences.

I finally got the macro to work properly-ish. It goes through the simulation several times but suddenly gives me an Err 9. I'll include the spreadsheet which is quite simple. NOTE: The declarations are made outside of the subs so that I don't have to re-enter them if I need to add other subs.

Code: Select all

Option Explicit

Dim Doc as Object, Sheet as Object, Cell1 as Object, Cell2 as Object, Cell3 as Object
Dim Choice as Integer, Count as Integer, Count2 as Integer, Count3 as Integer

Sub Main
	Doc = ThisComponent
	Sheet = Doc.Sheets.GetByName("Sheet1")
	On Error Goto ErrorReport 
	Sheet.GetCellRangeByName("J2").String = ""
	For Count3 = 1 to 100
		Sheet.GetCellRangeByName("J1").Value = Count3
		For Count = 1 to 7
			For Count2 = 1 to 30
				Sheet.GetCellByPosition(Count, Count2).String = ""
			Next Count2
		Next Count
		For Count2 = 1 to 30
			MakeChoice:
			Choice = int(8 * rnd)
			For Count = 1 to 7
				Cell1 = Sheet.GetCellByPosition(Count, Count2)
'				Sheet.GetCellRangeByName("I3").Value = Choice
				Select Case Cell1.Value
				Case Choice
					Goto MakeChoice
				Case 0
					Cell1.Value = Choice
					Exit For
				End Select
			Next Count
			Select Case Count
			Case <8
				Goto MakeChoice
			End Select
		Next Count2
		Cell2 = Sheet.GetCellRangeByName("J2")
		Cell3 = Sheet.GetCellRangeByName("B42")
		Select Case Cell3.Value
		Case 1
			Cell2.Value = Cell2.Value +1
		End Select	
	Next Count3
	End
	ErrorReport:
	Print "Error Code:  " & Err & " on line:  " & erl
End Sub

[Villeroy]

t105588.ods

(20.03 KiB) Downloaded 197 times

[Robitron]

t105588.ods

Thank you but that didn't answer the question. What is causing the Error 9 and how can I solve it?

[Villeroy]

I don't know.
A most simple macro for the job written in Python looks like this:

Code: Select all

import random

def main():
    doc = XSCRIPTCONTEXT.getDocument()
    sh = doc.Sheets.getByIndex(0)
    rg = sh.getCellRangeByName("B2:H31")
    da = list()
    for i in range(30):
        ra = range(1,8)
        random.shuffle(ra)
        da.append(tuple(ra))
    rg.setDataArray(tuple(da))

[Robitron]

Okay thanks. That really didn't help either since I'm using Basic and don't know python. But again, thanks for trying.

[Villeroy]

Your macro runs an endless loop until it runs into error 9 which indicates some buffer overflow somewhere as you can see when you remove your pointless error handler.
You can learn a serious scripting language such as Python in less than a week if you really want. In less than a week you can be more proficient with Python than you will ever be with StarBasic.
However, for the given task, filling a given cell range of 30x7 cells, the application's formula langauge is the best language you can use. It is the most efficient, least error prone and it works across all spreadsheet applications that are able to open the document.