I think that the correct answer is that you simply write your own function.
That way you do not need to copy the long messy function and replace some text. Based on what you just said, however, assume that the intent is that you will modify the active cell to refernece the values in question. That would look something like this: (now I go see if i can make it work). And much hinges on how fancy / smart you want to make the portion that figures out what cell is selected. Remember, it is possible that you select a range when you run the macro, but..... I know this is wrong because on my machine it complains about the "md" parameter, but I will not try to figure that out, it is 11:00 PM and I have not had my dinner yet. I hope that this helps at least a little bit. But this places the formula where you need it, you just need to (1) figure out how to call it and (2) what that formula needs to be. Let me know what you figure out. Dim s As String Dim sNew As String Dim sNewDates As String sNewDates = "D1, D2" sNewDates = InputBox ("Please enter Date Cells Example D2,D1:") ' Assume for a moment that you do want to do a simple string substitution. ' This will ONLY work if you are consistent on how "A2, A1" is represented in the string. ' The code you sent, it was not consistent. It was "A2, A2", A2 ,A2", and others. ' This example is consistent. ' Two double quotes works as an embedded double quote, so: s = "=IF(DATEDIF( A2, A1,""y""),IF(DATEDIF( A2, A1,""y"")=1,""1 Year "",DATEDIF( A2, A1,""y"")&"" Years ""),"""")&IF(MOD(DATEDIF(A2, A1 ,""m""),12),IF(MOD(DATEDIF( A2, A1,""m""),12)=1,""1 Month "",MOD(DATEDIF(A2, A1 ,""m""),12)&"" Months ""),"""")&IF(DATEDIF(A2, A1 ,""md""),IF(DATEDIF( A2, A1,""md"")=1,""1 Day "",DATEDIF(A2, A1,""md"")&"" Days""),"""")" ' You want to make a substitution. ' This gives you the string of interest. sNew = Replace(s, "A2, A1", sNewDates) Print sNew ' That said, what if you just do the entire calculation without using fancy dispatches. ' Even easier, you could write a function that is called from Calc and pass in two dates. Dim sDateCells() sDateCells = Split(sNewDates, ",") If UBound(sDateCells) <> 1 Then Print "Expected two dates" Exit Sub End If ' Get the current active sheet Dim oSheet oSheet = ThisComponent.CurrentController.getActiveSheet() Dim oRanges 'A blank range created by the document Dim oActiveCell 'The current active cell REM Force a single cell selection and discard the existing multi-selection if there is one. REM Create an empty SheetCellRanges service and then select it. REM This leaves ONLY the active cell selected. oRanges = ThisComponent.createInstance("com.sun.star.sheet.SheetCellRanges") ThisComponent.CurrentController.Select(oRanges) REM Get the active cell! oActiveCell = ThisComponent.CurrentSelection oActiveCell.setFormula(sNew) On Saturday, July 04, 2020 20:27 EDT, "Michael D. Setzer II" <msetze...@gmail.com> wrote: Basically, I want the macro to be able to work completely on its own. Agree that having to do the steps with the F2 and other keys is a problem. Not sure why the recorder refuses to record those steps?? The original formula is =IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1 Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1 Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 Day,DATEDIF(A2,A1,md)Days),) But to use the formula in a different location or with dates in different relative locations would require one to manually edit the formula and change all the A2,A1 settings to the new settings. Doing it 9 times. Thus the macro prompts for the new addresses, and changes them all. Problem is it leaves the formula contents displayed rather than the result?? Requiring the use of the F2 key and other keys. Have found the copy paste isn't required, but it changes the manual key options require to get the results. sub Z2 rem ---------------------------------------------------------------------- rem define variables dim documentas object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document document= ThisComponent.CurrentController.Frame dispatcher = createUnoService(com.sun.star.frame.DispatchHelper) rem ---------------------------------------------------------------------- dim sText sText = InputBox (Please enter Date Cells Example D2,D1:) rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name =StringName args1(0).Value ==SUBSTITUTE(SUBSTITUTE(=IF(DATEDIF(A2,A1,_y_),IF(DATEDIF(A2,A1,_y_)=1,_1 Year _,DATEDIF(A2,A1,_y_)_ Years _),__)IF(MOD(DATEDIF(A2,A1,_m_),12),IF(MOD(DATEDIF(A2,A1,_m_),12)=1,_1 Month _,MOD(DATEDIF(A2,A1,_m_),12)_ Months _),__)IF(DATEDIF(A2,A1,_md_),IF(DATEDIF(A2,A1,_md_)=1,_1 Day _,DATEDIF(A2,A1,_md_)_ Days_),__),A2,A1,chr$(34)sTextchr$(34)),_,CHAR(34)) dispatcher.executeDispatch(document,.uno:EnterString,, 0, args1()) msgbox (To complete process,+chr$(13)+Formula Press F2 then F9 then Enter then Enter+ chr$(13)+For Text Result Press F2 then F9 then Enter then F9 then Enter then Enter) end sub The ideal would be that the macro does the whole thing rether than requiring the user to press 4 or 6 additional key strokes?? On 5 Jul 2020 at 1:33, Regina Henschel wrote: Subject:Re: [libreoffice-users] Confused with Macro results?? To:users@global.libreoffice.org From:Regina henschelrb.hensc...@t-online.de Date sent:Sun, 5 Jul 2020 01:33:10 +0200 Hi Michael, your post is hard to understand. Please write, what you want to achieve with your macro. Kind regards Regina Michael D. Setzer II schrieb am 05-Jul-20 um 00:30: Been doing a lot of googling, but finding lots of stuff, but nothing that works? Have done a lot of versions and this is the latest one. The record process does not allow for the use of the F2 key, so as is, it results in the original args1(0).Value being in the cell? After the macro ends, I've found that if I manually press F2 then a space and enter it puts the formula as I originally wanted? F2 and enter does nothing?? I've also found that if I manually do F2 F9 Enter Enter, it will place just the final text result in cell. Seems there use to be a Keypress option, that would allow for simulating keys, but that has been deprecated? Also, found pages that talk about API stuff to modify cells, but the two I tried did nothing? Am I missing something?? Note: In string I replaced the originals with _ because it kept giving me messages about unbalanced ()?? That got rid of the errors, and then just converted them back? Not and important macro, but was just hoping to find a way to make it work the way I originally planned. Just playing around with stuff. Thanks. Perhaps I am overlooking something very simple.. sub Z1 rem ---------------------------------------------------------------------- rem define variables dim documentas object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document document= ThisComponent.CurrentController.Frame dispatcher = createUnoService(com.sun.star.frame.DispatchHelper) rem ---------------------------------------------------------------------- dim sText sText = InputBox (Please enter Date Cells Example D2,D1:) rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name =StringName args1(0).Value = =SUBSTITUTE(SUBSTITUTE(=IF(DATEDIF(A2,A1,_y_),IF(DATEDIF(A2, A1,_y_)=1,_1 Year _,DATEDIF(A2,A1,_y_)_ Years _),__)IF(MOD(DATEDIF(A2,A1,_m_),12),IF(MOD(DATEDIF(A2,A1,_m_),1 2)=1,_1 Month _,MOD(DATEDIF(A2,A1,_m_),12)_ Months _),__)IF(DATEDIF(A2,A1,_md_),IF(DATEDIF(A2,A1,_md_)=1,_1 Day _,DATEDIF(A2,A1,_md_)_ Days_),__),A2,A1,chr$(34)sText chr$(34)),_,CHAR(34)) dispatcher.executeDispatch(document,.uno:EnterString,, 0, args1()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document,.uno:Copy,, 0, Array()) rem ---------------------------------------------------------------------- REM Cut contents of cell to avoid the overwrite message dispatcher.executeDispatch(document,.uno:Cut,, 0, Array()) rem ---------------------------------------------------------------------- dim args2(5) as new com.sun.star.beans.PropertyValue args2(0).Name =Flags args2(0).Value =SVD args2(1).Name =FormulaCommand args2(1).Value = 0 args2(2).Name =SkipEmptyCells args2(2).Value = false args2(3).Name =Transpose args2(3).Value = false args2(4).Name =AsLink args2(4).Value = false args2(5).Name =MoveMode args2(5).Value = 4 dispatcher.executeDispatch(document,.uno:InsertContents,, 0, args2()) msgbox (To complete process,+chr$(13)+Formula Press F2 then Space then Enter+ chr$(13)+For Text Result Press F2 then F9 then Enter then Enter) end sub +------------------------------------------------------------+ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy