Static Random numbers [headache with conundrum -> 2000 lines of Basic]

English
,
#6

Thanks for your efforts.
The main reason for wanting the auto process on the starter sheet is, a few different people run the pool on Friday night so I would like the process to be as easy as possible ie minimal keyboard entry
I’ve attached the 3 relevant sheets (startup,names,GO8) would you be able to add the macro in with someway to trigger the macro to do its thing when the number of names entered = D2.
The start sheet is as it is now with the volitile random numbers starting in A7.
Go8 brackets are populated when the associated random numbers are set. The C column is a dropdown list (W/L) which then populates the next bracket with the winner etc. The green box is the table number (ours is 1-4)
Hopefully this copy works for you.
Your help is very much appreciated
Pool.ods (20.4 KB)

Garry

#13

It’s like shooting at sparrows with cannons, but with the help of a marginal benefit of Lupp’s BSM package, you can do it that way too:
nonVolatileRandarrayWithoutTemplateSheets.ods (34.8 KB)
Possibly there is one or another advanced user (@Villeroy ?, @Sokol92 ? … ) interested in the package. There is a template containing
the code and much more information about the usage, and there are more examples.

#64

Static Random numbers [headache with conundrum → 2000 lines of Basic]

No matter how many lines of code you throw at problems like this, you will never come close to the functionality of a database. This is a 100% database task.

1 Like
#65

shall we really just (pretend to) contribute with Argument from authority - Wikipedia — not to say Syllogism - Wikipedia ? :face_with_thermometer:

#66

He wonders about the lines of Basic code. I try to give a short explanation. I do not even understand what exactly he is trying to do. All I can see in the sample documents is record sets and relations.

1 Like
#67

though there was none !

ok, on this topic (since we’re trolling here) I 'm trying to find a relevant doc, or blog, or what, explaining the capabilities and limits of joins in Calc.
you may follow up on Extraire une liste d'une autre liste

more likely no one does, not even @Gazzal nor @joshua4.
this runaway thread should just have been locked long time ago :innocent:
Static Random numbers [headache with conundrum -> 2000 lines of Basic] - #60 by robleyd or we could start a wiki faq…

Calc: macro replacing cells connected to some date under some fix cell with the same date?
#68

All respect to all parties. When started, was under the impression OP had programming experience for some reason. And I didn’t realize about messaging vs. posting. I think the OQ is solved. Is there a way I can still help out the OP if need be?

#69

Respect to all.
This project did turn into a behemoth with 100+ VBA macros and 91 Named Expressions. Far outside my skill level.
I’m fairly new to LO too so have struggled along the way with all the above but I’m surely lost without Joshua there to sort out all the above…

We have a glitch getting the brackets set for the 2nd life and then the OQ will be solved.
Surely the community won’t cut this lifeline off at this point.

I have been PMing Josh but it appears you guys may have scared him off.
Please Josh lets get this final problem sorted out so I can make use of all this effort you’ve put in !!..

FYI - PM me by clicking on my handle and select ‘Message’ top right.
Garry

#7

Here is a bespoke solution using a BASIC macro. Although this is a pretty specific macro, the mechanics should be usable for other applications.

Sub CreateDerrangement()
	Dim Count As Long
	Dim FillRange As Object
	Dim FillCount
	Dim RandNums
	Dim Index
	Dim Subdex
	Dim Allow
		
	Count = ThisComponent.NamedRanges.getByName("PlayerCount").ReferredCells.DataArray(0)(0)
	FillRange = ThisComponent.NamedRanges.getByName("RandomSet").ReferredCells
	FillCount = FillRange.RangeAddress.EndRow - FillRange.RangeAddress.StartRow + 1
	If Count > FillCount Then Count = FillCount
	ReDim RandNums(0 To FillCount - 1)
	For Index = 0 To Count - 1
		RandNums(Index) = Array(0)
		RandNum = 1+INT(Rnd*Count)
		Allow = True
		For Subdex = 0 To Index - 1
			If RandNums(Subdex)(0) = RandNum Then
				Index = Index - 1
				Allow = False
				Exit For
			Else
			EndIf
		Next Subdex
		If Allow Or Index = 0 Then RandNums(Index)(0) = RandNum
	Next Index
	For Index = Index To FillCount - 1
		RandNums(Index) = Array(0)
	Next Index
	FillRange.setDataArray(RandNums)
	
End Sub

I’ve attached your Pool spreadsheet with the macro in place.

Pool.ods (32.5 KB)

#8

Hi Joshua
Thanks for that awesome and quick fix with my random number problem.
As I can’t seem to be able to alter anything you have done can you please help with the following;

I see you have included 2 ‘set names’ boxes which work a bit too well.
–Could you please delete the box on the starter sheet. [redundant with box on Go sheets and no reason for numbers to be seen here]
–With the box on Go8, can you make it so it either deletes or hides after one click so it can’t be activated many times, just the once.
[The person who does the draw could just bang the button and flick the names to whatever is best for themselves, as they play too. Once the names are set that’s the end of its job so can be done away with. As I see it?. Maybe they wouldn’t fiddle with it as it’s on screen but I’d rather not give them the option.]
–Can the background in box on Go8 be a light green? [to pretty it up].

–On the starter sheet can only the B7+ cells be highlighted based on the entered B4 cell value ie 10 players so B7-B16 only are colored. [Defines the correct number of names are in when all colored cells are filled]. Can the A column cells be blank on this sheet rather than showing ‘Unused’

I’ll eventually be using this sheet as a template as there are 17 other Go sheets (7-24) to cater for all the draw variations (number of players). So I’m hoping that your code will work the same way across all sheets.

I’ve done quite a bit of programming over the years, basic, VB, some COBOL/Pascal, some C++ etc but I can’t decipher that code you (or Gemini) come up with,
Again, your help muchly Appreciated!!.
Garry

#9

Tools>Forms>Design Mode toggles moving, marking, deleting buttons like Set Names. You add them using Insert>Form Control>Push Button.

Also, notice there have been some style applied. View>Sidebar then click on the A button toward the upper right of the sidebar. Then right click the style that is highlighted when the cell whose style you want to change is selected on the spreadsheet. from the drop menu (from right clicking) select Edit style, then Background tab (or Background in left pane perhaps in 25.2) and pick your color.

Styles, styles, styles! They let you design a dashboard then completely rework it with very little effort. There are even quicker ways (A with recycle arrows button in sidebar after editing a selected cell style) to update any style.

[READ then FORGET this paragraph…SEE BELOW: Anticipating your need for different bracket sheets, I also setup modular math for the initial and second-life positions. The 1 has to stay 1 at the top left (1st left position number) but the one right below it (2nd position number) can be copied down to 4th, and from then on the next pair (3rd and 4th position numbers, 2nd set of competitors) can be copied down indefinitely. They listen to the 8 up in B1 (or near there). The right pairs (second-life pairs) can be copied down in pairs indefinitely starting with the top pair (1st and 2nd position number to the right).]

EDIT: Further tested the bracket position numbers and redid them so they are based on a simple nth value column. See new version attached. You can just hide the numeric columns or format them so that the font is the same as the background (hence, invisible)–using styles! You can just pull down the (here darker grey) column right through, so you don’t have to maintain row spacing, you just have to have the 1, 2, 3, … sequence where needed in column A any how that works for a given bracket.

I also implemented @JohnSUN’s Peter-do-you-love-me approach to slowing down button clicks.

Pool.ods (33.9 KB)

#10

I understand what you are writing about. Yes, I have also encountered users who, due to absentmindedness, could press the button at the wrong time. Since blocking the button is not a trivial task, I decided to simply add three MsgBoxes with “yes/no” buttons and questions requiring different correct answers to the beginning of the macro:
Are you sure you want to generate a new tournament table?
Existing data will be destroyed - return to the current state?
Leave the table as is?
And only after receiving the “Yes-No-No” answers, I performed the rest of the actions.
The idea was that an absent-minded user would never read the texts in MsgBox and would simply always click Yes. It worked - no one corrupted the data.

#11

Hi Joshua
With the ‘Set Names’ button could your code go into the ‘Key Pressed’ event and the disable it go in the ‘Key released’ event.
Would that give me a one press only and no more
Silly or possible?
Garry

addendum

#12

As @JohnSun said, getting into the weeds of the UNO event model is seldom worth it. I’ve updated the file to just use a ‘secret’ cell to flag whether or not the button (really the macro itself) is operational. The macro checks for the value 1 in this cell and exits if it is not there. To set or reset the system so the button works (macro works), assign the ResetOneShot macro to a ‘secret’ keystroke. Tools>Customize…>Keyboard tab>Category pane>Application macros then select the ResetOneShot macro. Find your keystroke above, click on it, then press the Assign button. If you select your keystroke option first then the macro that’s fine, but you still have to press Assign. Now when you press that keystroke, you’ll get a popup saying the button has been reset, and it has been.
This system even persists across file loads. So, unless you have a power user working on it, only a person with the ‘secret’ keystroke can reset the button.
Bracket for Pool Gazzal.ods (35.5 KB)

#14

Hi Joshua
Many thanks for that program, works beautifully!.
I Love the MMath for the singles games!.
But I’ve sent you a workbook that includes a PAIRS match, Go8-P, which works like so.
Obviously there are 16 players.
Numbers are set and each pair goes in order ie;
1 & 2 (Reece & Kim), 3 & 4 (Sue & Brad) etc and then each pair is entered in the bracket as per singles. It’s just getting those numbers to work with yr system.
All I’ve done is stretched out the columns a bit to fit the two names in.
Can you please, again, show me how to do this on the example sheet.

There is a Go9-S sheet there proving the math works - Cool🌟
Garry

Bracket for Pool Gazzal.ods (42.8 KB)

#15

I may have spent too much time on this :upside_down_face:. But I think the project may be worthy of going onto the LibreOffice Extensions website as a template document, once completed.
Check out the automation. The 8 Pair sheet has lots of comments.
I reworked formatting using a mixture of styles and conditional formatting. Very little single-cell formatting should be needed. Really, just setting the cells that have visible content to be styled as Entry will go a very long way.
Many columns can now just be pulled down for however tall the bracket is. Other columns can be mimicked pretty easily. Please do read the comments on the example sheet.
Please post back how things are going, and I’m happy to lend a hand for final touches before posting on the Extensions site.
If you are not a seasoned user of Excel or Calc, understanding this automation will not be easy!! But using it to ‘paint’ the brackets actively should be pretty straightforward.
Brackets for Pool Auto.ods (46.2 KB)
Oh, to be honest, I’m not sure if the Winner… functions need to be entered as array formulas or not. It seems to work either way, and is a real testament to the internals of Calc. I think entering as an array formula (even though just one cell) is the ‘safer’ option.
Also, merged cells are ‘evil’. Avoid them whenever possible…

#16

HI
Thanks for that tremendous effort done so quickly too. Nice looking form!!.

On pressing the Set Names box [Shift/F7 to run] an ‘exception occurred’ which hasn’t happened b4. Type: com.sun.star.container.NoSuchElementException
In this line; Count = ThisComponent.NamedRanges.getByName(“PlayerCount”).ReferredCells.DataArray(0)(0)

I have been trying to decipher your notes so have been playing around a little bit. So not sure if it’s your new stuff or me.

I’ve noticed you’ve taken over my ‘Table Number’ green boxes with yr TD arrows “C” so I’m gonna have to take the green boxes back and put yr TD arrow in next column about .17cm wide?. Funny; I did something on the sheet and pop, all the TD arrows went haywire and Ctrl Z didn’t help, looked a right sight. So a bit of work to get them back on track.
I’ve included the sheets again with a few adjusted TD’s but haven’t worked out how to get the green background yet for the ‘Table’ box. Needs a table style?.
Still not sure if on starting this program that the ‘Enter Players’ field on starter sheet resets to zero and names are cleared or not, do they?.

Still reading at kindergarten level😂

Garry

Brackets for Pool Auto.ods (46.2 KB)

#17

Repaired the PlayerCount problem. It is part of preparing to put the random list per sheet…I don’t think having a single random list is going to be convenient in the end. That requires some data vectoring, so not today.

Added back little pool tables for the pool table numbers and abstracted the named expressions to allow that sort of thing more easily, but it wasn’t difficult, really. Since these cells need to hold the table numbers, they have to be formatted as such to the Location style, but you can copy a whole game unit (connector boxes, table number, player names, win/loss) at once, so that shouldn’t matter much.
Brackets for Pool Auto.ods (42.3 KB)

You might try building a sheet for a four-player bracket, no extra life, or something like that to see if the “painting” with the named expressions works.

Oh, and if you get the 504 errors, try Ctrl+Shift+F9 let me know if they persist…seem to be a quirk in Calc itself. If so, comment on your LO version, too, by copying the Help>About information with the little copy button on the About splash dialog.

#18

Thanks
I have quickly made a TEST 8-S sheet for your critique.
Problems with this version;
The Set Names box has lost it’s magic (msgbx) so I can smack the numbers willy nilly
The B col random numbers are not unique
Garry

Brackets for Pool Auto (2).ods (51.1 KB)

#19

Lots of advancement. Look forward to:

  • No distinction between single-player and pair sheets–one sheet does it all for 8 players or 8 teams
  • Each bracket sheet has its own player list–multiple simultaneous tournaments are possible
  • Player lists can be persisted (stored) – keep the Monday night crowd list and have another for Fridays
  • Player list entry gives count affirmation (like you had) plus flags repeat entries
  • Player raw list moved from names sheet to player list sheet. This is ?? but seemed easier to, say, correct a spelling or add a name quickly
  • Final playoff (center of sheet) is now auto-populated
  • Lots of bug fixes

However, the 504 error is not totally gone yet.
Notice that the one-shot protection is intentionally disabled for testing.
Deadwood random numbers are just left sitting at the left of the player list right now…they are ignored (I think :slight_smile:).

Edit: Corrected scripting error

Brackets for Pool Auto Jo4.ods (51.4 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.