Libreoffice Base Default Date Today
I have promised for a very long time to start writing about the scripting language of programming Basic in LibreOffice and creating macros by this language. This article is devoted to the types of data is used in Basic and, to a greater extent, the rules of description and the possibility of using variables. As always, I will try to provide a maximum of information, and for this reason I hope that this simple topic will be useful not only for novice users. Separately, I would like to thank everyone who commented on the Russian article, gave their recommendations, and helped to deal with difficult questions.
Insert the current date and current time into a new database record? I will use this site and look for your answers to BASE questions as I go through the process of learning a data base system after years of spreadsheet dependence in our family business. Get the highlights in your inbox every week. I have long promised to write about the scripting language Basic and creating macros in LibreOffice. This article is devoted to the types of data used in LibreOffice Basic, and to a greater extent, descriptions of variables and the rules for using them. I will try to provide enough information for.
Variable naming conventions
The names of variables can not contain more then 255 symbols. Any name of variables should be started from capital or small letters of the Latin alphabet or underscores ('_'). Also you can use numeral a variable name. Some other characters of punctuation and a non-Latin alphabet cause to appear message 'Syntax error' or 'BASIC Runtime Errors', if that names are not put within square brackets.
As you can see, you can use localized variable names. Whether it makes sense to do so is up to you. Does it make sense to use? It's up to you. The opportunity exists.
Note: In examples that contain square brackets, if you remove the brackets, macros will show a window with an error
Declaring variables
Strictly speaking, it is not necessary to declare variables in LibreOffice Basic, except for arrays. If you write a macro from a pair of lines, and this macro will work with small documents, then the declaration of variables can be neglected. In this case, the variable will automatically be declared as the Variant type. In all cases where the macro is more or it should work in a large document, it is strongly recommended that you declare variables. First, it increases the readability of the text. Second, it allows you to control variables, that can greatly facilitate the search for errors. Third, the Variant type is very resource-intensive, and considerable time is needed for the hidden conversion. In addition, the Variant type chooses not the optimal variable type for data, which also increases the workload of computer resources.
Basic provides the ability to automatically assign a variable type by its prefix (the first letter in the name) to simplify the work if you prefer to use the Hungarian notation. For this, the statement DefXXX is used and after it the letter with which variables of this type begin. XXX in this case the letter type designation. A statement with a letter will work in the module, and must be specified before subprograms and functions appear. There are 11 such types:
- DefBool - for boolean variables;
- DefInt - for integer variables of type Integer;
- DefLng - for integer variables of type Long Integer;
- DefSng - for variables with a single-precision floating point;
- DefDbl - for variables with double precision floating point type Double;
- DefCur - for variables with a fixed point of type Currency;
- DefStr - for string variables;
- DefDate - for date and time variables;
- DefVar - for variables of Variant type;
- DefObj - for object variables;
- DefErr - for object variables containing error information.
If you already have an idea of the types of variables in LibreOffice Basic, you probably noticed that there is no Byte type in this list, but there is for a strange beast with the Error type. Unfortunately, you just need to remember this. I have not yet found the answer to this question. This method is convenient, since the type is assigned to the variables automatically. But it does not allow you to find errors related to typos in variable names. In addition, it will not be possible to specify non-Latin letters, that is, all names of variables in square brackets that need to be declared must be declared explicitly.
In order to avoid accidental typos when using declared variables explicitly, you can use the statement OPTION EXPLICIT. This statement should be the first line of code in the module. All other commands, except comments, should be placed after it. This statement tells the interpreter that all variables must be declared explicitly, otherwise it produces an error. Naturally, this statement makes it meaningless to use the Def statement in the code.
A variable is declared using the statement Dim. You can declare several variables simultaneously, even different types, if you separate their names with commas. To determine the type of a variable with an explicit declaration, you can use either a corresponding keyword or a type-declaration sign after the name. If a type-declaration sign or a keyword is not used after the variable, then the Variant type is automatically assigned to it. For example:
Variable types
In LibreOffice Basic, seven classes of variables are supported:
- Logical variables contain one of the values: TRUE or FALSE.
- Numeric variables contain numeric values. They can be integer, integer positive, floating-point, and fixed-point.
- String variables contain character strings.
- Date variables can contain a date and/or time in the internal format.
- Object variables can contain objects of different types and structures.
- Arrays.
- Abstract type Variant.
Logical variables – type Boolean
Variables of the Boolean type can contain only one of two values: TRUE or FALSE. It should be understood that in the numerical equivalent, the value FALSE corresponds to the number 0, and the value TRUE corresponds to -1 (minus one). But any value other than zero passed to a variable of the Boolean type will be converted to TRUE, that is, converted to a minus one. You can explicitly declare a variable in the following way.
I did not find a special symbol for it. For an implicit declaration, you can use the DefBool statement. For example:
The initial value of the variable is set to FALSE. A Boolean variable requires one byte of memory.
Integer Variables
To integer variables there are 3 types: Byte, Integer and Long Integer. These variables can only contain integers. When you transfer numbers with a fractional part into such variables, rounding occurs according to the rules of classical arithmetic (and not to the big side, as it is written in the help). The initial value for these variables is 0 (zero).
Type Byte
Variables of the Byte type can contain only integer positive values in the range from 0 to 255. Do not confuse this type with the physical size of information in bytes. Although we can write down a hexadecimal number to a variable, the word Byte only indicates the dimensionality of the number. You can declare a variable of this type as follows:
There is no a type-declaration sign for this type. There is no the statement Def of this type. By reason of the small dimension, this type will be most convenient for a loop index, the values of which do not go beyond the range. A Byte variable requires one byte of memory.
Type Integer
Variables of the Integer type can contain integer values from -32768 to 32767. They are convenient for fast calculations in integers and are suitable for a loop index. '%' is a type-declaration sign. You can declare a variable of this type in the following ways:
For an implicit declaration, you can use the DefInt statement. For example:
Type Long Integer
Variables of the Long Integer type can contain integer values from -2147483648 to 2147483647. Long Integer variables are convenient in integer calculations, when the range of type Integer is insufficient for the implementation of the algorithm. '&' is a type-declaration sign. You can declare a variable of this type in the following ways:
For an implicit declaration, you can use the DefLng statement. For example:
A Long Integer variable requires four bytes of memory.
Numbers with a fractional part
All variables of these types can take positive or negative values of numbers with a fractional part. The initial value for them is 0 (zero). As mentioned above, if a number with a fractional part is assigned to a variable capable to contain only integers, LibreOffice Basic rounds the number according to the rules of classical arithmetic.
Type Single
Single variables can take positive or negative values in the range from 3.402823x10E+38 to 1.401293x10E-38. Values of variables of this type are Single-precision floating-point formatIf it is explain dactylic, in this format only 8 numeric characters are stored, and the rest is stored as a power of ten (the number order). In the Basic IDE debugger, you can see only 6 decimal places, but this is a blatant lie. Computations with variables of the Single type take longer time than with Integer variables, but are faster than computations with variables of the Double type. A type-declaration sign is '!'. You can declare a variable of this type in the following ways:
For an implicit declaration, you can use the DefSng statement. For example:
Type Double
Variables of the Double type can take positive or negative values in the range from 1.79769313486231598x10E308 to 1.0x10E-307. Why such a strange range? Most likely in the interpreter there are additional checks that lead to such a strange situation. Values of variables of the Double type are double-precision floating-point format and can have 15 decimal places. In the Basic IDE debugger, you can see only 14 decimal places, but this is also a blatant lie. Variables of the Double type are suitable for precise calculations. Calculations require more time than for the Single type. A type-declaration sign is '#'. You can declare a variable of this type in the following ways:
For an implicit declaration, you can use the DefDbl statement. For example:
A variable of the Double type requires 8 bytes of memory.
Type Currency
Variables of the Currency type are displayed as numbers with a fixed point and have 15 signs in the integral part of a number and 4 signs in fractional. The range of values includes numbers from -922337203685477.6874 to +92337203685477.6874. Variables of the Currency type are intended for exact calculations of monetary values. A type-declaration sign is '@'. You can declare a variable of this type in the following ways:
For an implicit declaration, you can use the DefCur statement. For example:
Type String
Variables of the String type can contain strings in which each character is stored as the corresponding Unicode value. They are used to work with textual information and, in addition to printed characters (symbols), can also contain non-printable characters. I do not know the maximum size of the line. Mike Kaganski experimentally set the value to 2147483638 character, after which LibreOffice falls. This corresponds to almost 4 Gigabytes of characters. A type-declaration sign is '$'. You can declare a variable of this type in the following ways:
For an implicit declaration, you can use the DefStr statement. For example:
The initial value of these variables is an empty string ('). The memory required to store string variables depends on the number of characters in the variable.
Type Date
Variables of the Date type can contain only date and time values stored in the internal format. In fact, this internal format is the double-precision floating-point format (Double), where the integer part is the number of days, and the fractional is part of the day (that is, 0.00001157407 is one second). The value 0 is equal to 30.12.1899. But the Basic interpreter automatically converts it to a readable version when outputting, but not when loading. You can use the Dateserial, Datevalue, Timeserial, or Timevalue functions to correctly and quickly convert to the internal format of the Date type. To extract a certain part from a variable in the Date format, you can use the Day, Month, Year, or Hour function, Minute, Second functions. The internal format allows us to compare the date and time values by calculating the difference of two numbers. There is no a type-declaration sing for the Date type, so if you explicitly define it, you need to use the Date keyword.
For an implicit declaration, you can use the DefDate statement. For example:
Type of Object Variables
Convention-based, we can take two variables types of LibreOffice Basic to Objects.
Type Objects
Variables of the Object type are variables that store objects. If we do not go into details, then the object is any isolated part of the program that has the structure, properties and methods of access and data processing. For example, a document, a cell, a paragraph, dialog boxes are objects. They have a name, size, properties, and methods. In turn, these objects also consist of objects, which in turn can also consist of objects. Such a 'pyramid' of objects is often called an object model, and it allows us, when developing small objects, to combine them into large ones. Through a larger object, we have access to smaller ones. And this allows us to operate with our documents, to create and process them, while abstracting from a specific document. There is no a type-declaration sing for the Object type, so for an explicit definition, you need to use the Object keyword.
For an implicit declaration, you can use the DefObj statement. For example:
The variable of type Object does not store in itself an object, but is only a reference to it. The initial value for this type of variables is Null.
Structures
The structure is essentially an object. And if you look in the Basic IDE debugger, then most of them you will see the Object type. Not everything. Some will have not the Object type (for example, the structure of the Error has the type Error). But roughly speaking the structures in LibreOffice Basic are simply grouped into one object variables, without special access methods. Another significant difference is that when declaring a variable of the Structure type, we must specify its name, rather than the Object. For example, if MyNewStructure is the name of a structure, the declaration of its variable will look like:
There are quite a lot of built-in structures. But the user can create personal. Structures can be convenient when we need to operate with sets of heterogeneous information that should be treated as a single whole. For example, create a tPerson structure.
The definition of the structure should go before subroutines and functions that use it.
To fill a structure, you can use the following method. For example, the built-in structure com.sun.star.beans.PropertyValue:
For a simpler filling of the structure, you can use the With operator.
The initial value is only for each variable in the structure and corresponds to the type of the variable.
Type Variant
This is a virtual type of variables. The Variant type is automatically selected for the data to be operated on. The only problem is that the interpreter does not need to save our resources, and it does not offer the most optimal variants of variable types. For example, it does not know that 1 can be written in Byte, and 100000 in Long Integer, although it reproduces a type, if the value is passed from another variable with the declared type. And besides, the transformation itself is quite resource-intensive. Therefore, this type of variables is the slowest of all. If you need to declare this kind of variables, you can use the Variant keyword. But you can omit the type description altogether, the Variant type will be assigned automatically. There is no a type-declaration sing for this type.
For an implicit declaration, you can use the DefVar statement. For example:
This variables type is assigned by default to all not declared variables.
Type Arrays
Arrays are a special type of variables in the form of a data set, more reminiscent of a mathematical matrix, except that the data can be of different types, and allowing one to access its elements by index (element number). Of course, a one-dimensional array will be similar to a column or row, and a two-dimensional array will be like a table. There is one feature of arrays in LibreOffice Basic, which distinguishes it from other programming languages. Since we have an abstract type of Variant, then the elements of the array do not have to be homogeneous. That is, if there is an Array with three elements under numbers from 0 to 2, and we write the name in the first element of Array(0), in the second Array(1) age, and in the third Array(2) the weight of the person, we can have respectively, the type values for Array(0) is String, for Array(1) is Integer, and for Array(2) is Double. In this case, the array will resemble a structure with the ability to access the element by its index. Array elements can also be homogeneous, other arrays, objects, structures, strings, or any other data type used in the LibreOffice Basic.
Arrays must be declared before they are used. Although the index space can be in the range of type Integer, that is, from -32768 to 32767, by default the initial index is selected as 0. You can declare an array in several ways:
You can change the lower bound of an array (the index of the first element of the array) by default using the Option Base statement that must be specified before using subprograms, functions, and defining user structures. Option Base can only take two values 0 or 1, which must follow immediately after the keywords. The action applies only to the current module.
Source and additional information:
< Documentation How Tos
| < Previous Section | Next Section > |
List of Calc Date & Time functions
| TODAY | returns today's date. |
| NOW | returns the current date and time. |
| YEAR | returns the year of a given date. |
| MONTH | returns the month of a given date. |
| DAY | returns the day of a given date. |
| WEEKDAY | returns the day of the week for a given date. |
| HOUR | returns the hour of a given time. |
| MINUTE | returns the minute of a given time. |
| SECOND | returns the seconds of a given time. |
| DATE | returns the date, given the year, month and day of the month. |
| DATEVALUE | returns a date-time serial number from a text date. |
| TIME | returns the time, given hours, minutes and seconds. |
| TIMEVALUE | returns a date-time serial number from a text time. |
| EOMONTH | returns the date of the last day of a month. |
| EDATE | returns a date a number of months away. |
| WORKDAY | returns a date a given number of workdays away. |
| NETWORKDAYS | returns the number of workdays between two dates. |
| DAYS | returns the number of days between two dates. |
| DAYS360 | returns the number of days between two dates, using the 360 day year. |
| DAYSINMONTH | returns the number of days in the month of the given date. |
| DAYSINYEAR | returns the number of days in the year of the given date. |
| WEEKS | returns the number of weeks between two dates. |
| WEEKSINYEAR | returns the number of weeks in the year of the given date. |
| WEEKNUM | returns the ISO week number of a given date. |
| WEEKNUM_ADD | returns the non-ISO week number of a given date. |
| MONTHS | returns the number of months between two dates. |
| YEARS | returns the number of years between two dates. |
| ISLEAPYEAR | tests if a date is in a leap year. |
| EASTERSUNDAY | returns the date of Easter Sunday in a given year. |
| YEARFRAC | returns the number of years including fraction between two dates. |
Date and Time overview
In Calc, dates and times are represented by numbers. For example the number 39441 may represent the date 25Dec07. We may enter 39441 in a cell, and then (selecting Format - Cells..) choose a date format to display the number as a date.
Although a number representing a date / time is the same as any other number (except that we choose to display it as a date or time) it may be helpful to use the term 'date-time serial number'. The date-time serial number is simply the number of days that have passed since a set starting date. The default (normal) starting date is 30 December 1899; choose Tools - Options - OpenOffice.org Calc - Calculate to change this to 1 January 1904 for Apple software, or 1 January 1900 for old StarCalc 1.0 software if necessary.
Times are represented as a fraction of a day - for example 0.5 is half a day, or 12 hours, or 12 noon; 0.25 is a quarter of a day, or 6 am. Thus 39441.25 represents 6am on 25Dec07.
It would be extremely inconvenient if, in order to enter a date in a cell, we had to work out the date-time serial number, enter it and then change the display format. Therefore Calc tries to do this for us: if we enter 25Dec07, Calc recognises that this is a date, converts it to the date-time serial number, and sets the format to display as a date. Calc recognises a wide range of possible date entries - for example 25-Dec-07, 25 December 2007, Dec 25 07, and 25/12/07 or 12/25/07 (these last two depend on the date system you use - whether days or months are written first).
If we only enter 2 digits of the year (for example 07 instead of 2007) Calc needs to know whether we mean 2007 or 1907. In Tools - Options - OpenOffice.org - General you may set the range of years for which Calc will recognise years given 2 digits.
Some of Calc's date and time functions return a 'date' or a 'time' ( for example TODAY() ). This is simply the date-time serial number, but if the cell is unformatted, Calc will display the date or time rather than a number format.
The descriptions of the Calc functions in these help pages use the international standard ISO date format for clarity, because this does not depend on locale; for example, 23rd May 2009 is shown as 2009-05-23.
Tips and Tricks
All major spreadsheets (including Calc) handle dates and times in a similar way, for historical reasons, but this can be a bit cumbersome. Here are some helpful examples:
The date two weeks from now
- TODAY()+14. Simply add 14 days, and format the cell as a date to display it correctly.
How many days between two dates
- DAYS('23Jan08'; '9Jan08') returns 14, the number of days - however you can simply subtract one date from the other; for example if cell A1 contains 23Jan08 and cell A2 contains 9Jan08, A1-A2 also returns 14.
- Use NETWORKDAYS if you wish to know how many working days; for example NETWORKDAYS(A2; A1) returns 11, the number of whole workdays (including both end days but excluding weekends). NETWORKDAYS can also take account of other non-working days.
A column with dates a week apart
- Format the column to display dates as you prefer. Enter the first date at the top of the column (let's say that is cell A2). Enter =A2+7 in cell A3. Copy A3, and paste down the rest of the column.
A column showing the 15th day of consecutive months
- Format the column to display dates as you prefer. Enter the first date at the top of the column (say cell A2). In cell A3 enter =EDATE(A2; 1). Copy A3, and paste down the rest of the column.
A column showing the 15th day of months, quarterly (eg 15Jan, 15Apr, 15Jul, 15Oct)
- Format the column to display dates as you prefer. Enter the first date at the top of the column (say cell A2). In cell A3 enter =EDATE(A2; 3). Copy A3, and paste down the rest of the column.
A column showing the last day of consecutive months
- Format the column to display dates as you prefer. Enter the first date at the top of the column (say cell A2). In cell A3 enter =EOMONTH(A2; 1). Copy A3, and paste down the rest of the column.
The last working day of the month
- WORKDAY(EOMONTH(A1;0)+1;-1) finds the date of last weekday (Monday-Friday), where cell A1 contains the month or a date in that month. See WORKDAY() for how to cater for holidays and other non-working days.
Summing hours and minutes (more than 24 hours)
- Select the cells to sum; from the menu: Format - Cells..; Category = User-defined; Format code = [HH]:MM. You can now enter hours and minutes, for example 15:15, 25:30. Summing them gives 40:45, displayed correctly.
Summing minutes and seconds (more than 60 minutes)
- Use the example for hours and minutes above, but pretend that you are entering minutes and seconds, rather than hours and minutes. So entering 15:15 means 15 minutes 15 seconds.
Years before 1925
- Advanced topic:
- Gregorian and Julian calendars
Descargar word gratis para windows 10. Please improve this app. ,226000000,269,null,null,'3','gemmalyn Pilayan',null,null,2,null,null,null,'Alons',null,2,null,null,null,'many glitches, no benefits for having it in this day and age, defeats the purpose of high end smartphones because it is not properly optimized.
- The calendar system we use today is called the Gregorian calendar. Every leap year, the month of February has 29 days instead of 28. We often think that leap years occur every four years (which works well for present day dates), but in fact with our Gregorian calendar the years 1800, 1900, 2100, 2200 .. are not leap years; a leap year occurs if the year is divisible by 4 and also not divisible by 100, or if it is divisible by 400. This is simply a convention that tries to take account of the time it takes the earth to go round the sun.
- The Gregorian calendar was adopted in different countries at different times. In Italy and Spain the day after 4th October 1582 was 15th October 1582. In Great Britain the change was made in 1752. The USA changed between 1582 and 1867 (different areas at different times). The last countries (for example Greece) changed as late as the 1920s.
- Calc calculates all dates back to 15th October 1582 using the Gregorian calendar. The day before this is 4th October 1582, and Calc uses the Julian calendar for dates before this.
- In the Julian calendar used before 4th October 1582, leap years occur if the year is divisible by 4 - that is every 4 years; for example 1500 is a leap year.
- However Calc functions should not be used with dates before 15th October 1582 - for example DAY('1582-10-04') returns 14 rather than 4.
- Obviously some care is needed when interpreting early dates, even with dates as recent as 1924, to ensure that the dates are relevant to the country or situation.
- Very early dates, for example 8AD and before, should not be used in any event, as the calendar systems were uncertain.
- The external webpage Frequently Asked Questions about Calendars has more information.
- Portability
- Excel states that the first date in its date system is either 1st January 1900, or 2nd January 1904 (depending on settings); earlier dates are not valid.
- There is a known issue in Excel - it calculates year 1900 as a leap year, for historical reasons (apparently for compatibility with an earlier spreadsheet), whereas in the Gregorian calendar 1900 is not a leap year. This only affects the months of January and February in 1900, as Excel's date system is not valid before that. Later years such as 2100, 2200 are correctly calculated.
- Calc correctly identifies that 1900 is not a leap year, and Calc's date system is valid before that date, as described above.
Financial date systems
- Advanced topic:
- In the days before computers became widespread, various systems were devised to make manual date calculations easier. Some of these are still in use, and are available in Calc, notably in Financial Functions. This unfortunately makes life today a little harder.
- Days between two dates; days in a year
- One issue here is to determine the number of days between two given dates, date1 and date2 (date2 after date1), respectively day1, month1, year1 and day2, month2, year2, according to a basis of calculation as follows:
- 0: - US method (NASD), 12 months of 30 days each (30US/360)
- NASD was the forerunner to FINRA (both are US institutions). The system is:
- 1. If both date1 and date2 are both the last day of February, day2 is changed to 30.
- 2. If day1 is 31 or date1 the last day in February, day1 is changed to 30.
- 3. If day1 is now 30 and day2 is 31, day2 is changed to 30.
- NASD was the forerunner to FINRA (both are US institutions). The system is:
- Each month is now assumed to have 30 days, and the result calculated.
- Unfortunately Excel implements this system incorrectly - it omits step 1. For compatibility Calc now gives the same result as Excel.
- Notice that under this system the (number of days between date1 and date2) is not necessarily the same as -1 * (number of days between date2 and date1).
- 1: - Actual number of days in months, exact number of days in year
- This system is simply the normal calendar; 28 days in February or 29 days in a leap year. The result is DAYS(date2; date1). The number of days in a year is 365, 366 or somewhere in between (to be advised).
- 2: - Actual number of days in month, year has 360 days
- The number of days between two dates is calculated as for basis 1. Whole years are counted as 360 days each. For example, the number of days between 2008-01-01 and 2009-01-01 is 366 (a leap year); the year between those dates has 360 days.
- 3: - Actual number of days in month, year has 365 days
- The number of days between two dates is calculated as for basis 1. Whole years are counted as 365 days each. For example, the number of days between 2008-01-01 and 2009-01-01 is 366 (a leap year); the year between those dates has 365 days.
- 4: - European method, 12 months of 30 days each (30E/360)
- If either day1 or day2 is 31, it is changed to 30. Each month is now assumed to have 30 days, and the result calculated.
- Other financial date systems are in use, but have not been implemented in Excel or Calc.
- Whole months before or after a given date
- Another issue is to determine a date that is a whole number of months or years before or after another date. This is important for example in the COUPDAYBS function.
- The rule seems to be that if the original date is the last day of a month, then the new date is also the last day of the month; otherwise the same day of the month is used (or the nearest possible). Thus:
- 6 months before 2008-09-15 is 2008-03-15
- 6 months before 2008-08-31 is 2008-2-29 (leap year)
- 6 months before 2008-2-29 is 2007-08-31
- 6 months before 2007-08-29 is 2007-2-28 (not leap year)
- 1 year before 2009-02-28 is 2008-02-29
- Functions listed alphabetically,