When I change a value in one of the cells J2-J5 I need the values in I10- I39 to be recalculated. I was expecting the result from the income sub to appear but am getting 0.
Please see attachment for code.cost comparison.ods
To threads concerning Calc (and not specifically only aspects of the view) please don’t attach/supply Images or the like but real spreadsheet files.
To simply replace the original text of a question and the original attachments a week later spoils the thread. Someone coming later to study it, and probably to find a solution to his similar question can no longer understand my original answer, the EDITs I made to it, and the comment on this by you and myself.
That’s annoying. Next time please use editing in a way allowing for an understanding of the ongoing thread as I did when editing my answer.
Quoting @GaryDown: “When I change a value in one of the cells J2-J5 I need the values in I10- I39 to be recalculated. I was expecting the result from the income sub to appear but am getting 0.”
This is no longer the case. If AutoCalculate is enabled, the recalculation is done automatically on any change in the parameter range. Otherwise you need to trigger it explicitly.
If no changes are shown this may be due to a change in parameters that doesn’t actually influence the results. Concerning the results in column I this the case for J3 eg (as already mentioned earlier). In the current situation the very small value in J4 might be the culprit.
The majority of locales uses the comma as the decimal separator. Others use the point. To avoid silly misuderstandings ISO 31-0 explicitly deprecates the usage of both these characters (comma, point) as a “group separator”. We shouldn’t ignore international standards.
Lupp,
Thanks for your help, unfortunately as with this comment there is no way to add new attachments when commenting. How do you add a new attachment to each comment.
I am sorry if I have missed something but there is no “attachment” button here.
Thanks,
Gary.
You can only attach to questions and answers, but you can leave the original and append something properly marked without destroying the “history”. If you overwrite former content and links, answers and comments may no longer be understandable, and may even look “crazy”.
I dislike this askbot software anyway. We have to bear it, but we shouldn’t increase damage.
As you see below I edited my answer 4 times to date without removing what I had written/attached earlier.
===Final Editing===
Information very relevant for the answer is given in the seventh comment below containing the bold words “Don’t go that way”. What I told there is exemplified in the attachment to the “EDIT4” section.
===End Final Editing===
The statements Income = Income + thisYearAmount orders a recursive call to the Income function without the needed parameters.
It’s surprising that you don’t report a script error that should have occurred if the code ran into this line.
In what way did you apply the function? To a range or to the cells J2:J5 one by one? What parameters were passed in what way? How should we know? (I did not analyse the code in detail yet.)
Everything else: First provide a sheet where you get the reported error, but not the one to expect.
===Edit1===
The Object not set error is due to the fact that the .DataArray property not is a 2D array as you would Dim it in Basic, but a 1D array (a column) of 1D arrays (a row each). Thus you need (e.g.) to order (e.g.)
costPer = oData(1)(0) instead of what you wrote.
Numbers as elements of a DataArray always are Double (IEEE 754) as the cell valus are. Conversion to the appropriate integer type is done automatiocally if needed. The Basic Type Integer has an upper limit of 2^15-1 (16-bit integers). 32-bit-integers are type Long.
I don’t understand for what reason the earlier mentioned usage of Income as an expression does not throw an error.
===Edit2===
See this rectified example.
===Edit3===
See this reworked attachment.
===Edit4 2018-12-19 13:08 CET regarding the recent comment by the OQ===
Quoting @GaryDown (the OQ): “Yes, the costper is not used in income but is planned for other calculations we need to do.”
Never conceal such information. It may gravely mislead those wanting to help and may result in wasted time.
Concerning my recent suggestion:
(Once again: For heaven’s sake, don’t go the Outsourcing-to-a-Sub-Do. It’s worst youtube-Excel-VBA-style.)
Well, I did what I suggested. This was the easiest part of my involvement here.
See this newly reworked attachment.
I have attached the sheet to the original question.
After closing out and then restarting libreoffice to open I get
Basic runtime error, object not set.
I have managed to get it working (new sheet attached to question) but have 1 question.
Line 23 in the setPrivates sub ('costPer = oData(1)(0)) blows an error if included. Is it caused by the format/content of the cell J3/M3? If so how do I fix it?
You again Dim’d costPer as Integer. The part of my answer appended by editing tells why this won’t work.
You again had the version with oData(0,0) which cannot work due to the actual structure of oData.
Suggestion: Always use Double for parts working with Calc data. These are always Double. If the automatic conversion does not meet you needs, convert excplicitly, but always use the most capable tinteger type which is Long. The “extended universe of numeric types” Basic offers can mostly be restricted to Basic-internal use.
You still use the name of your function in an expression position which should regularly start a recursive call. Why do you accept the risk to fail with this under the next version? Simply use an extra variable for the iteration/accumulation.
By the way my considerations (verified by testing) show that the inner for-loop always is enterd once at maximum.
See new attachment to my answer.
Lupp,
thanks for your time and effort. I understand what you are suggesting.
Unfortunately, I am still don’t understand what is happening…when I change a value in one of the cells j2-j5 I am expecting the cells i10-i39 to be recalculated but it doesn’t appear they are.
Also, what is the correct way to trap execution in the sub/function so I can stp through it and see what’s going on. I thought I had that working before but now it seems I have forgotten something as the breakpoints are not stopping execution.
Once again,
Thanks for your your assistance.
Quoting @GaryDown: “…I change a value in one of the cells j2-j5 I am expecting the cells i10-i39 to be recalculated but it doesn’t appear they are.”
I wouldn’ write a personalised tutorial every time a basical misunderstanding has a revival in new clothes.
If I find the time I will post a question about this and answer it myself to make things detectable to a broader public.
One error is specific to your case: The variable costPer is Dim’d and gets assigned a value, but is never used in an expression. It thus cannot have any effect on the final result of the Income() function.
I will attach another reworked version of your file to my answer above. It contains a Q&D workaround in I10:I39 relieving the no-AutoRecalc-problem.
Thanks again Lupp
Yes, the costper is not used in income but is planned for other calculations we need to do. My next step is to separate the accessing of the cells out to a subroutine. after everything is working.
Cheers.
Is this no-AutoRecalc-problem an ongoing problem? The Q&D workaround doesn’t seem to work all the time either.
Quoting @GaryDown: “My next step is to separate the accessing of the cells out to a subroutine.”
Don’t go that way!
The appropriate way to pass anything functionally acting as a parameter of a function via formal parameters (argument positions). Your failure to do so was the reason for what your function not was able to act under AutoCalculate as you had expected. My Q&D repairs exacly made the range of additional parameters a factual - though fake - parameter not passing any values.
The correct and clean way to get that behaviour is to omit the typeOfCalc parameter and the internal access to fix ranges controlled by it and to replace it with a parameter generalValues for passing the actual values as an array which then can be filled with J$2:J$5 or M$2:M$5 or whatever applying.
If you want to try my suggestion you should know that the array passed this way will have the ordinary 2D structure instead of the special structure used by your oDataArray.
Thanks again Lupp.
I am not sure (yet) what you mean but will keep learning.
If you study the example attached to my “EDIT4” amendment to my answer, everything should be clear.
Lupp, thanks for all your help. I have followed your suggestions and have completed (first stage) of the sheet. All working now. I have replaced the upload with the new version.
Cheers.
Lipp, thanks for all the advice. the key (for me) was your comment of 19 Dec.
The appropriate way to pass anything functionally acting as a parameter of a function via formal parameters (argument positions). Your failure to do so was the reason for what your function not was able to act under AutoCalculate as you had expected. My Q&D repairs exacly made the range of additional parameters a factual - though fake - parameter not passing any values.
The correct and clean way to get that behaviour is to omit the typeOfCalc parameter and the internal access to fix ranges controlled by it and to replace it with a parameter generalValues for passing the actual values as an array which then can be filled with J$2:J$5 or M$2:M$5 or whatever applying.
Cheers,
Gary.
Glad to hear your issue is solved.
You closed the thread with this label: “Closed for the following reason the question is answered, right answer was accepted by GaryDown.”, but you didn’t accept the answer in the sense the askbot expects: By clicking the checkmark left of the answer.
Other users looking for answers to their similar questions, find the relevamt info visibloe to them in the grren checkmark shown if the answer was actually accepted. For closing a thread there are dfifferent resons.