Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 18 Jan 2020 08:31:36 +0100How to create a user defined functionhttps://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/ How do I get a function that says if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2Fri, 22 Jun 2018 18:55:35 +0200https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/Comment by Lupp for <p>How do I get a function that says if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2</p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=158737#post-id-158737The term "user defined function" for Calc is commonly supposed to mean a function added - based on user code, that's by programming - to the standard functions of Calc. What you got suggested by @hjek and accepted as a correct answer is not something of that kind. In commonly used terms it is a **formula** (aka spreadsheet-formula"). Given the literal subject the other answer was correct though probably not very helpful to you. No reason, however, to downvote it. Serious attempt to help!Sat, 23 Jun 2018 01:01:55 +0200https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=158737#post-id-158737Answer by EasyTrieve for <p>How do I get a function that says if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2</p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?answer=225589#post-id-225589In [OpenOffice.org Macros Explained - OOME_3_0 by Andrew Pitonyak -669 pages](http://www.pitonyak.org/OOME_3_0.pdf), on page 514 or section 15.7 is 'Write your own Calc functions'.
**Here is another example to fill in for a missing function in standard Calc:**
This Basic Macro adds a new Calc function that can format a number as a string using the Basic (not Calc) **`format`** function to do the work, for example you can now format a number as a string with leading zeros or the like:
Function FORMAT_(Number As Integer, sFormat As String) As Variant
Dim s$ : s=format(CStr(Number), sFormat)
Format_ = s
End Function
*Note that this could not simply be named **Format**, as that name conflicts with the other built in function named Format. (I think it's strange that Calc can't seem to use that built in function, but that is something to figure out another day. Also I was surprised that I couldn't find a function in Calc to format a number as a string, perhaps it's there by a name that I just haven't found yet.)*
**If you just want to keep it simple** this works the same as the code above:
Function FORMAT_(N, s) As Variant : Format_ = format(CStr(N), s) : End Function
**Then call your new Basic macro in a cell formula** just like any other function, with the name followed by a parenthesized list of parameters, like this: `=FORMAT_(A15,"0#") & "/" & FORMAT_(B15,"0#") & "/" & C15` to format a fixed width date string from Day, Month, and Year integer columns. The `0#` string parameter says to output a string where if the 10's numeral (in a 2 digit number the left most numeral) is missing then display a zero, and a numeral 0-9 for the right most digit. I.e. 5 --> "05", and 15 --> "15".
**TIPS:**
* Once you've created your Basic Macro, to get it to work, you must do two things: First save your Calc doc. Then do `Menu` | `File` | **`Reload`** and be sure to click on **`Enable Macros`** when it reloads. This will cause Calc to read the new list of available Basic Macros.
* Also after you make any further edits to your macro, you can hit the `F9` key to cause Calc to re-calculate using the newly edited macro code.Sat, 18 Jan 2020 08:31:36 +0100https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?answer=225589#post-id-225589Answer by Xoristzatziki for <p>How do I get a function that says if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2</p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?answer=158715#post-id-158715Every [macro](https://help.libreoffice.org/Common/Macro) written in LO BASIC as FUNCTION can be used as function in Calc. LO BASIC Functions, when used as Calc functions, get the current cell as parameter and return a value to the cell.
Edited by @Lupp:
*More precise: The parameters passed to the function are described by the parameter list appended (enclosed in parentheses) to the function's name. Each parameter may be a constant or a reference or a formula again. When called from a cell's formula the function gets passed parameters always as if specified 'ByVal' in Calc. (Excel may do it otherwise.)*Fri, 22 Jun 2018 19:27:42 +0200https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?answer=158715#post-id-158715Comment by EasyTrieve for <p>Every <a href="https://help.libreoffice.org/Common/Macro">macro</a> written in LO BASIC as FUNCTION can be used as function in Calc. LO BASIC Functions, when used as Calc functions, get the current cell as parameter and return a value to the cell. </p>
<p>Edited by <a href="/en/users/11185/lupp/">@Lupp</a>: <br>
<em>More precise: The parameters passed to the function are described by the parameter list appended (enclosed in parentheses) to the function's name. Each parameter may be a constant or a reference or a formula again. When called from a cell's formula the function gets passed parameters always as if specified 'ByVal' in Calc. (Excel may do it otherwise.)</em></p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=225576#post-id-225576@Lupp, Thank you this was what I was looking for.Sat, 18 Jan 2020 04:28:33 +0100https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=225576#post-id-225576Comment by EasyTrieve for <p>Every <a href="https://help.libreoffice.org/Common/Macro">macro</a> written in LO BASIC as FUNCTION can be used as function in Calc. LO BASIC Functions, when used as Calc functions, get the current cell as parameter and return a value to the cell. </p>
<p>Edited by <a href="/en/users/11185/lupp/">@Lupp</a>: <br>
<em>More precise: The parameters passed to the function are described by the parameter list appended (enclosed in parentheses) to the function's name. Each parameter may be a constant or a reference or a formula again. When called from a cell's formula the function gets passed parameters always as if specified 'ByVal' in Calc. (Excel may do it otherwise.)</em></p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=225575#post-id-225575@Calcasieu You should read this essential book. It's the best out there that I've yet found: [OpenOffice.org Macros Explained - OOME_3_0 Andrew Pitonyak -669 pages](http://www.pitonyak.org/OOME_3_0.pdf)Sat, 18 Jan 2020 04:27:01 +0100https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=225575#post-id-225575Comment by Calcasieu for <p>Every <a href="https://help.libreoffice.org/Common/Macro">macro</a> written in LO BASIC as FUNCTION can be used as function in Calc. LO BASIC Functions, when used as Calc functions, get the current cell as parameter and return a value to the cell. </p>
<p>Edited by <a href="/en/users/11185/lupp/">@Lupp</a>: <br>
<em>More precise: The parameters passed to the function are described by the parameter list appended (enclosed in parentheses) to the function's name. Each parameter may be a constant or a reference or a formula again. When called from a cell's formula the function gets passed parameters always as if specified 'ByVal' in Calc. (Excel may do it otherwise.)</em></p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=158717#post-id-158717Thanks for your answer. I am not proficient in Libre and am not familiar with the terms LO BASIC. Can you refer me to a source that explains this in more basic terms?Fri, 22 Jun 2018 20:01:04 +0200https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=158717#post-id-158717Answer by hjek for <p>How do I get a function that says if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2</p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?answer=158720#post-id-158720It's fairly simple to literally translate your requirements
> if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2
to a formula
=IF(OR(ISBLANK(B2), ISBLANK(C2)), "", C2/B2)
I think your question is good and precise, but the headline is misleading, because [User-Defined Functions](https://help.libreoffice.org/Calc/User-Defined_Functions) has to do with using macros to defined named functions, whereas your problem can be solved just fine with a simple formula.
(If this works for you, please click on the ✓ to accept the answer.)Fri, 22 Jun 2018 20:22:45 +0200https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?answer=158720#post-id-158720Comment by Calcasieu for <p>It's fairly simple to literally translate your requirements</p>
<blockquote>
<p>if B2 or C2 is empty the result is empty, but otherwise C2 is divided by B2</p>
</blockquote>
<p>to a formula</p>
<pre><code>=IF(OR(ISBLANK(B2), ISBLANK(C2)), "", C2/B2)
</code></pre>
<p>I think your question is good and precise, but the headline is misleading, because <a href="https://help.libreoffice.org/Calc/User-Defined_Functions">User-Defined Functions</a> has to do with using macros to defined named functions, whereas your problem can be solved just fine with a simple formula.</p>
<p>(If this works for you, please click on the ✓ to accept the answer.)</p>
https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=158728#post-id-158728Works perfect. Many thanks.Fri, 22 Jun 2018 21:52:51 +0200https://ask.libreoffice.org/en/question/158712/how-to-create-a-user-defined-function/?comment=158728#post-id-158728