How do I write a macro for current date in Calc?

[sam81]

New user coming from Excel.

It’s strange but the code below works sometimes, and other times it doesn’t.It’s from Excel VBA. On error, it’s telling me ‘Basic runtime error. ‘13’ Data type mismatch’ and highlights the row with ‘For Each Mycell in Selection’. Can this code be edited slightly to fix it?

Sub TodaysDate()
    For Each Mycell In Selection
    Mycell.Activate
       ActiveCell.FormulaR1C1 = "=TODAY()"
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False
    Next Mycell
End Sub  

(Slightly edited by @Lupp to get two additional lines into the code range.)

[Lupp]

Well, with Option VBAsupport 1 it should work.

However,
-1- For what good?
-2- If you want to inserte a fixed date, you should also set a reasonable format.
-3- If you actually want to do this for more than one cell at a time, you shouldn’t be restricted to a single range.

The following code should be very efficient in Calc, relieve the restriction to one single CellRange, and allow to apply at the same time a properly defined CellStyle. On my old computer it needs about 70 µs per cell (7 s for 100 000 cells. Your code eats 13 s for 1000 cells that’s a factor of about 180.

Sub insertFixDateIntoSelectedCells() REM Multiselection accepted.
mySpreadsheetDocument = ThisComponent
cSel = mySpreadsheetDocument.CurrentSelection
If cSel.SupportsService("com.sun.star.sheet.SheetCellRanges") Then
  selectedCellRanges = cSel : u = selectedCellRanges.Count - 1
Else 
  selectedCellRanges = Array(cSel) : u = 0
End If
csDate = "csISO8601"
REM You need to replace this cellstyle name with the one you
REM actually want to use. Please consider the only globally well 
REM understood "YYYY-MM-DD" format (ISO 8601).
For j = 0 To u
  j_rg = selectedCellRanges(j)
  j_rg.CellStyle = csDate
  j_rg.ArrayFormula = "=INT(NOW())"
  j_da = j_rg.GetDataArray
  j_rg.setDataArray(j_da)
Next j
End Sub

[schiavinatto]

Hi, @sam81

Sub InsertCurrentDate
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController.Frame, ".uno:InsertCurrentDate", "", 0, Array())
End Sub

[sam81]

New problem, when I assign the macro to a button it works. If I save the file, close it, and re-open it, the button seems to lose its link to the macro and I have to re-assign it.

[Lupp]

Please use add a acomment to add a comment or to ask a question in return. Misusing the answer tool for the purpose cannot even preserve the order.
And:
“”
If this “new problem” actually is closely related to the specific answers given here, make clear in what way.
“”
If it’s just about a problem with supplying and/or calling macros generally, please post a new question, and tell precisely what you tried and in what way it faioled.

[schiavinatto]

@sam81,

Record the macro in the file, NOT in My Macros.