Excel - analysis of spins on casino roulette

To avoid writing separate macros in Sheet or Book, use classes. Alt2005 just wrote a call to the class of the needed copy of the sheet. You can recalculate via call .calculate. At the same time, do not forget to disable events, so that event processing does not enter an infinite loop.

Now you can look at the macros in which alt2005 described special functions. You can't do without Visual Basic programming skills. But if we want to get the necessary functions for Excel, we'll have to figure it out

Public Function getResult(z As Range, distance As Long, repeats As Long, Optional w = 0) As Variant
Dim i As Long
Dim bottomAddresses as long
Dim for As Long
Dim counter as long
Dim sum as long
Dim umsatz As Long
Dim num As Long
Dim ss As String
Dim d As Long

bottomAdres = z.Row - distance + 1
If bottomAddress < 1 Then
getResult = 0
Exit Function
End If
counter = 1
d = 0
For i = z.Row - 1 To bottomAdres Step -1
d = d + 1
za = Worksheets(z.Worksheet.Name).Cells(i, 1)
If z = z.Value Then
counter = counter + 1
End If
If counter = repeats Then
Exit For
End If
Next i
If counter < repeats Then
getResult = 0
Exit Function
End If
For i = z.Row + 1 To z.Row + distance
umsatz = umsatz + 1
za = Worksheets(z.Worksheet.Name).Cells(i, 1)
If z = z.Value Then
sum = sum + 35
If w = 0 Then
getResult = sum
End If
If w = 1 Then
getResult = umsatz
End If
If w = 2 Then
getResult = i
End If
Exit Function
Else
sum = sum - 1
End If
Next i
getResult = sum
If w = 1 Then
getResult = umsatz
End If
End Function


Public Function getNextZ(z As Range, za As Long, Optional w = 0) As Variant
Dim bottom as long
Dim i As Long
Dim valuation as option
Dim cou as long
Dim startVal As Long
Dim zc As Long

startVal = z.Value
For i = z.Row + 1 To z.Row + 600
cou = cou + 1
zc = Worksheets(z.Worksheet.Name).Cells(i, z.Column)
If zc = za Then
If w = 0 Then
getNextZ = cou
End If
If w = 1 Then
getNextZ = i
End If
Exit Function
End If
Next i
getNextZ = 0
End Function


Public Function getPredZ(z As Range, za As Long, Optional w = 0) As Variant
Dim bottom as long
Dim i As Long
Dim valuation as option
Dim cou as long
Dim startVal As Long
Dim zc As Long

startVal = z.Value
bottom = z.Row - 600
If bottom < 1 Then
bottom = 1
End If
For i = z.Row - 1 To bottom Step -1
cou = cou + 1
zc = Worksheets(z.Worksheet.Name).Cells(i, z.Column)
If zc = startVal Then
If w = 0 Then
getPredZ = cou
End If
If w = 1 Then
getPredZ = i
End If
Exit Function
End If
Next i
getPredZ = 0
End Function

Quick Reference Guide to VB Operators.

Abs (function) - returns the absolute value of a number
And (operation) - logical AND
AppActivate (operator) - activates the application window
Array (function) - creates an array from parameters and returns it as a Variant variable
Asc (function) - returns the numeric code of the first character of the argument string
Atn (function) - returns the arctangent of a number in radians
Fan (operator) - plays a sound signal through the computer speaker
Call (operator) - transfers control to a module procedure (Sub), a module function (Function), or a DLL subroutine
CBool (function) - converts an expression to Boolean type
CByte (function) - converts an expression to type Byte
ССur (function) - convert expression to Currency type
CDate (function) - convert expression to Date type
CDbl (function) - conversion to Double type
ChDir (operator) - changes the current directory on the device
ChDrive (operator) - changes the current device
Choose (function) - returns a value from the argument list with a specific ordinal number
Chr (function) - returns the character associated with a specific numeric code
CInt (function) - convert expression to Integer type
CLng (function) - convert expression to Long type
Close (operator) - closes a file opened by the Open operator
Command (function) - Returns the command line used to launch a Visual Basic or VB application
Const (operator) - constant declarations
Cos (function) - returns the cosine of a number
Create Object (function) - create an OLE Automation object
CSng (function) - convert expression to type Single
CStr (function) - convert expression to String type
CurDir (function) - returns the current directory of the logical device
CVar (function) - convert expression to Variant type
CVErr (function) - returns the error subtype for a user-defined error number
Date (operator) - sets the system date value
Date (function) - returns the system date value
DateAdd (function) - returns a Variant variable containing a date that differs from the specified date by a certain time interval
DateDiff (function) - returns the number of time intervals between two dates
DatePart (function) - returns a specific part of a given date
DateSerial (function) - returns the date for the given year, month and day
DateValue (function) - returns the date
Day (function) - returns a number from 1 to 31 corresponding to the current day of the month
DDB (function) - returns the value of depreciation losses for a specified period
Declare (operator) - at the module level declares references to external routines in the DLL
Deftype (operators) - sets the default data type at the module level for variables, subroutine parameters, and return values for functions and Property Get operators that begin with certain characters
Dim (operator) - declares variables and allocates memory for them
Dir (function) - returns the name of a file or directory matching the given pattern or file attribute, or the volume label of a device
DoEvents (function) - interrupts the execution of the application
Do... Loop (operator) - repeats a block of commands until a condition is true or until a condition becomes true
End (operator) - ends a subroutine or command block
Environ (function) - returns a string associated with an operating system environment variable
EOF (function) - returns a value indicating whether the end of the file has been reached
Eqv (operator) - checks the logical equality of two expressions
Erase (operator) - reinitializes fixed-size array elements and reallocates memory for dynamic arrays
Error (operator) - emulates the occurrence of an error
Error (function) - returns the text of the message of the given error number
Exit (operators) - exits from Do ... Loop, For... Next cycles, functions and procedures
Exp (function) - returns the exponent of a number
FileAttr (function) - returns the open mode or file number (handle)
FileCopy(onepamop) - copies a file
FileDateTime (function) - returns the date and time of creation or last modification of a file
FileLen (function) - returns the length of the file in bytes
Fix (function) - returns the integer part of a number
For Each...Next (operator) - repeats the same sequence of commands for each element of an array or collection
For...Next (operator) - repeats a sequence of commands a certain number of times
Format (function) - formats the expression according to the specified format
FreeFile (function) - returns the next unoccupied file number for use in the Open statement
Function (operator) - declares the name, arguments and code of a subroutine that returns a value (function)
FV (function) - returns the annuity value based on periodic payments and a constant investment rate
Get (operator) - reads data from an open file into a variable
GetAttr (function) - returns attributes of a file, directory, or volume label
GetObject (function) - returns an OLE Automation object for a file with the given extension
GoSub... Return (operator) - executes a subroutine
GoTo (operator) - transfers control to a specific line of a subroutine without returning control
Hex (function) - returns a string representing the hexadecimal value of a number
Hour (function) - returns an integer in the range 0 - 23 inclusive, representing a specific hour of the day
If...Then...Else (operator) - execution of groups of commands depending on the value of an expression
Iff (function) - returns one of two values depending on the value of the expression
Imp (operation) - implication of two expressions
Input (function) - returns characters from a file opened for sequential access or as a binary file
Input # (operator) - reads data from an open file into variables
InputBox (function) - shows an input dialog box, waits for text input and returns the contents of the entered text after the window is closed
InStr (function) - returns the position of the first found substring in the string
Int (function) - returns the integer part of a number
Is (operation) - comparison of two object references
IsArray (function) - returns a boolean value indicating whether the given variable is an array
IsDate (function) - returns a boolean value indicating whether the expression can be converted to the Date type
IsEmpty (function) - returns a boolean value indicating whether the value of the given variable is initialized
IsError (function) - returns a boolean value indicating whether the expression is an error code value
IsMissing (function) - returns a boolean value indicating whether the given optional parameter was passed to the subroutine
IsNull (function) - returns a boolean value indicating whether the expression contains an invalid (Null) value
IsNumeric (function) - returns a boolean value indicating whether the given expression can be considered a number
IsObject (function) - Returns a Boolean value indicating whether the expression is an OLE Automation object
Kill (operator) - deletes file
LBound (function) - returns the value of the lower bound of the array index
LCase (function) - returns a string in lowercase
Left (function) - returns a certain number of characters from the beginning of the string
Len (function) - returns the number of characters in a string or the number of bytes required to store a variable
Let (operator) - assigns the value of an expression to a variable or property
Like (operation) - comparison of two strings
Line Input # (operator) - reads a line from a file into a variable
Load (operator) - loads a form or control into memory
LoadPicture (function) - loads a graphic image into objects: Form,
Loc (function) - returns the current read/write position in an open file
Lock (operator) - controls access of other processes to all or part of an open file
LOF (function) - returns the size in bytes of an open file
Log (function) - returns the natural logarithm of a number
LSet (operator) - copies a string into a string variable, and also copies the value of a variable of one specialized type into a variable of another specialized type
LTrim (function) - returns a copy of the string without leading spaces
Mid (operator) - replaces a certain number of characters in a string with characters from another string
Mid (function) - returns a certain number of characters from a certain position in a string
Minute (function) - returns an integer in the range 0 - 59 representing the minute of the hour
MkDir (operator) - creates a new directory
Mod (operation) - returns the remainder of the division of two numbers
Month (function) - returns an integer in the range 1 - 12 representing the month number
MsgBox (function) - displays a message in a dialog box, waits for the user to select one of the buttons, and returns a value indicating which button was selected
Name (operator) - renames a file or directory
Not (operation) - logical negation
Now (function) - returns the current date and time values
Oct (function) - returns a string representing the octal representation of a number
On Error (operator) - sets up an error handler and specifies the location of the handler routine; also used to cancel error handling by the handler routine
Оn..GoSub, On...GoTo (operators) - transfer of control to one of several specific lines (labels), depending on the value of the expression
Open (operator) - hides the file for input/output
Option Base (operator) - used to declare the value of the lower bound of the default array index dimension
Option Compare (operator) - used at module level to declare the default comparison method when comparing strings
Option Explicit (operator) - used at the module level to set the declaration check for all variables in the module
Option Private (operator) - used at module level to indicate that the entire module is Private
Or (operation) - logical OR
Partition (function) - returns a string indicating how many times numbers from a given range occurred
Print # (operator) - writes formatted data to a file
Private (operator) - used at module level to declare Private variables and allocate memory space for storing them
Property Get (operator) - declares the name, arguments, and code of the property value retrieval routine
Property Let (operator) - declares the name, arguments, and code of the procedure for setting the value of a property
Property Set (operator) - declares the name, arguments, and code of the procedure for setting a reference to an object
Public (operator) - used at module level to declare Public variables and allocates memory space for storing them
Put (operator) - writes a variable to a file
QBColor (function) - returns the RGB code corresponding to the color number
Randomize (operator) - initializes the random number generator
RGB (function) - returns an integer representing the RGB code value
ReDim (operator) - used at the subroutine level to redefine the size of dynamic arrays and allocate memory space for them
Rem (operator) - insert comments into the program
Reset (operator) - closes all files opened by the program
Resume (operator) - continues program execution after the error handler procedure has completed
Right (function) - returns a certain number of characters from the right side of a string
RmDir (operator) - deletes a directory
Rnd (function) - returns a random number
RSet (operator) - copies the right part of a string into a string variable
RTrim (function) - returns a copy of the string without trailing spaces
SavePicture (operator) - saves a graphic image of a Form object, Picture Box or Image controls to a file
Second (function) - returns an integer value in the range 0 - 59, representing the second in a minute
Seek (operator) - sets the position for the next read or write operation to an open file
Seek (function) - returns the current read/write position of an open file
Select Case (operator) - executes one or more commands, depending on the value of the expression
SendKeys (operator) - sends one or more keystrokes to the active window as if they were entered by the user from the keyboard
Set (operator) - binds an object reference to a variable or property
SetAttr (operator) - sets file attributes
Sgn (function) - returns the sign of a number
Shell (function) - launches an external program for execution
Sin (function) - returns the sine value of an angle
Space (function) - returns a string containing a specified number of spaces
Spc (function) - positioning in the output line
Sqr (function) - calculates the square root of a number
Static (operator) - used at module level to declare variables and allocates memory space for their storage. Variables retain their values until the program ends
Stop (operator) - pauses the program execution
Str (function) - returns a string representation of a number
StrComp (function) - returns the result of string comparison
StrConv (function) - returns the converted string
String (function) - returns a string of a given length from identical characters
Sub (operator) - declares the name, parameters and body of a procedure
Switch (function) - Calculates the values of a list of expressions and returns a value or expression associated with the expression in the list whose value is True
Tab (function) - positioning in the output line
Tan (function) - returns the tangent value of an angle
Time (operator) - sets the system clock value
Time (function) - returns a Date value indicating the current system time
Timer (function) - returns the number of seconds since midnight
TimeSerial (function) - returns a Date value containing the time for the specified hour, minute, and second
Time Value (function) - returns a Date type value containing the time of day
Trim (function) - returns a copy of the string without leading and trailing spaces
Type (operator) - declares a specialized data type at the module level
TypeName (function) - returns a string of information about the specified variable
UBound (function) - returns the value of the largest index for a given array dimension
UCase (function) - returns a string converted to uppercase
Unload (operator) - unloads a form or control from memory
Unlock (operator) - controls access of other processes to all or part of an open file
Val (function) - returns the numeric representation of a string
VarType (function) - returns a value indicating the type of the variable
Weekday (function) - returns an integer representing the day of the week
While...Wend (operator) - executes a sequence of commands in a loop until a condition is true
Width # (operator) - sets the width of the output line for the write operation to an open file
With (operator) - executes a sequence of commands for a specific object or variable of a specialized type
Write # (operator) - writes data to a file
Хоr (operation) - exclusive OR
Year (function) - returns an integer representing the year

Visual Basic data types:

Boolean - logical type. 2 bytes are allocated for storing the variable. The variable can only take 2 values True and False.

Byte - numeric type. 1 byte is allocated for storing the variable. The variable value can be in the range from 0 to 255. The sign is not used.

Char - character type. 2 bytes are allocated for storing the variable. Characters are usually used as the variable value, since 2 bytes are now used. Developers can use Unicode characters.

Date - is used to store dates. The variable takes up 8 bytes. Possible values range from January 1, 100 AD to December 31, 9999.

Decimal is the most powerful numeric type. 16 bytes are allocated for storing a variable of this type. The negative and positive boundaries of the range in which the possible values of this variable are located are the same in magnitude and equal to +/- 79,228,162,514,264,337,593,543,950,335 if integers are used. If it is necessary to store fractional values, the boundaries of possible values will be shifted by several orders of magnitude depending on how many decimal places the developer uses. This type can only be used to store decimal fractions. The developer can use up to 28 decimal places.

Double is a numeric type. It is used to store numbers in exponential form. 8 bytes are allocated for storing the variable. Negative values are in the range from - 1.79769313486231E+308 to -4.94065645841247E-324. Positive values are in the range from 4.94065645841247E-324 to 1.79769313486231E+308.

Integer - is designed to handle integer values. A variable of this type takes up 4 bytes. Possible values are in the range from - 2,147,483,648 to 2,147,483,647.

Long - is intended for integer values. A byte is allocated to store the variable. Possible values of a variable of this type are in the range from - 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Object - essentially, a variable of this type is just a reference to a specific instance of an object. 4 bytes are allocated for storing the variable.

Short - lightweight integer type. 2 bytes are allocated for storing the variable. Possible values of a variable of this type are in the range from - 32,768 to 32,767.

Single - is designed to store numbers in exponential form. 4 bytes are allocated for storing the variable. Negative possible values of a variable of this type are located in the range from - 3.402823E+38 to -1.401298E-45. Positive values are in the range from 1.401298E-45 to 3.402823E+38.

String - string type. Designed to store strings of varying length. The possible length of a string can reach up to 2 million Unicode characters. The amount of memory for storing the variable is allocated depending on the length of the string.

An array is a set of variables of the same type with the same name. Each such memory cell is an array element, and each has its own ordinal number that determines its place. The first element is always assigned the number 0.

To declare an array, use the following syntax:
Dim | Public | Private ArrayName(Subscript) As DataType

· Dim | Public | Private – a Visual Basic keyword that defines the scope of its visibility (see the Variables section above).
· ArrayName – the name of the array.
· Subscript – the number of the last element in the array (for example, if this number is 6, then there will be seven elements in the array; remember that element numbers in an array start from zero).
· As is a Visual Basic keyword that precedes a type declaration.
· DataType – any valid Visual Basic data type, such as Integer or Double.

Therefore, the declaration of an array of five elements of type "Integer" should look like this:
Dim MyArray(4) As Integer

To assign a value to each element of this array, you must specify the name of the array and add the number of the corresponding array element in parentheses:
MyArray(0) = 9
MyArray(l) = 342
MyArray(2) = 2746
MyArray(3) = 0
MyArray(4) = 8901

Although the number of elements in an array is usually determined when it is declared, sometimes it is necessary to change the dimensions of an array during program execution. Changing the number of elements in an array is called redefining the array dimensions. To change the number of elements in an existing array, use the "ReDim" keyword and the following syntax:
ReDim [Preserve] ArrayName(Subscript) [As DataType]
· ReDim is a Visual Basic keyword that means to change the dimensions of an array.
· Preserve is an optional Visual Basic keyword that specifies whether to preserve the previous values of array elements. If you do not use this keyword when resizing an array, the values of all elements will be zero for numeric data types, an empty string for a string type, and "Empty" for a Variant type.
· ArrayName — the name of the array.
· Subscript - the index of the last element in the array.
· As is a Visual Basic keyword that precedes a data type declaration.
· DataType — any valid Visual Basic data type. When redefining the array size, this parameter is optional, since the type cannot be changed. The array type can be redefined with the ReDim keyword only if the array is of type Variant.

Our Excel experts find this funny, but we have to start somewhere. They can help with tips on how to work with ranges or the best ways to solve various problems.

I'm creating a new book.
In column A I write a small random set of numbers in the range 0-36

=RANDBETWEEN(0;36)






If you need to fix the generation, then it needs to be copied as values.