[Solved] Help me convert Excel VBA into Basic Macro

[penguinoid]

Hello!
I'm trying to convert my Excel VBA macro into OpenOffice Calc Basic macro, but I have some difficulties understanding how OpenOffice's objects work (dispatcher, etc.)
I would appreciate your help. My VBA code:

Code: Select all

Option Explicit

Sub CreateSheetForEachDayOfYear()
    
    Dim arrHolidays As Variant, arrShortDays As Variant

    Dim datStartOfTheYear As Date, datEndOfTheYear As Date, datSingleDay As Date
    Dim varCounter As Variant
    
    datStartOfTheYear = #1/1/2020#
    datEndOfTheYear = #12/31/2020#
        
    arrHolidays = Array(#1/1/2020#, #1/2/2020#, #1/3/2020#, #1/4/2020#, #1/5/2020#, #1/6/2020#, #1/7/2020#, #1/8/2020#, #2/23/2020#, #2/24/2020#, #3/8/2020#, #3/9/2020#, #5/1/2020#, #5/9/2020#, #5/11/2020#, #6/12/2020#, #11/4/2020#)
    arrShortDays = Array(#4/30/2020#, #5/8/2020#, #6/11/2020#, #11/3/2020#, #12/31/2020#)

    Application.ScreenUpdating = False
    
    For datSingleDay = datStartOfTheYear To datEndOfTheYear
        
        Sheets.Add After:=ActiveSheet
        ActiveSheet.Select
        ActiveSheet.Name = Format(datSingleDay, "dd mmm")
        
        Range("A1:J1").Merge
        With Range("A1")
            .HorizontalAlignment = xlCenter
            With .Font
                .Size = 14
                .Bold = True
            End With
            .Value = StrConv(Format(datSingleDay, "dddd, "), 3) & Format(datSingleDay, "dd mmmm yyyy")
        End With
        
        For Each varCounter In arrHolidays
            If CDate(varCounter) = datSingleDay Then
                Range("A1").Font.Color = 255
                ActiveSheet.Tab.Color = 255
            End If
        Next
        
        For Each varCounter In arrShortDays
            If CDate(varCounter) = datSingleDay Then
                Range("A1").Font.Color = 12611584
                ActiveSheet.Tab.Color = 12611584
            End If
        Next
        
        If Weekday(CDate(datSingleDay)) = 1 Or Weekday(CDate(datSingleDay)) = 7 Then
            Range("A1").Font.Color = 255
            ActiveSheet.Tab.Color = 255
        End If

        Range("A3").Select
        
    Next
    
    Application.DisplayAlerts = False
    shtButtonSheet.Delete
    Application.DisplayAlerts = True
    Worksheets(1).Select
    Application.ScreenUpdating = True
    
End Sub

[Villeroy]

Run that macro in Excel, save the resulting document in whatever format and use it with whatever spreadsheet application.

[Lupp]

(Spoken aside:)
To create a separate sheet for every day of the year is a popular idea, but mostly a bad ONE.
To define holidays by typing their dates as texts into an array-variable assignment inside BASIC code is another ONE.
To use the internationally deprecated mid-endian format MM/DD/YYY for the purpose is a third ONE.
To use sheet names (meant to be short dates now in another deprecated and very unusual way) with spaces is...
To change the date format in another place then in a hard-coded way is...
...

[JeJe]

Its not going to be possible in one document because there is a maximum of 256 sheets... you'd have to use more than one.

This may help you get started if you still want to do this... It creates a sheet, merges some cells, sets them to bold and size 14 font, and sets the string

Code: Select all

Sub CreateSheetForEachDayOfYear()


	Dim arrHolidays As Variant, arrShortDays As Variant

	Dim datStartOfTheYear As Date, datEndOfTheYear As Date, datSingleDay As Date
	Dim varCounter As Variant

	datStartOfTheYear = cdate("1/1/2020")
	datEndOfTheYear = cdate("31/12/2020") 'I CHANGED THIS AROUND AS WHERE I AM ITS DAYS THEN MONTH

	arrHolidays = Array("1/1/2020", "1/2/2020", "1/3/2020", "1/4/2020", "1/5/2020", "1/6/2020", "1/7/2020", "1/8/2020", "2/23/2020", "2/24/2020", "3/8/2020", "3/9/2020", "5/1/2020", "5/9/2020", "5/11/2020", "6/12/2020", "11/4/2020")

	arrShortDays = Array("4/30/2020", "5/8/2020", "6/11/2020", "11/3/2020", "12/31/2020")


	For datSingleDay = datStartOfTheYear To datEndOfTheYear

		st = Format(datSingleDay, "dd mmm")
		thiscomponent.Sheets.insertnewbyname (st,0)
		sheet=      thiscomponent.Sheets.getbyname (st)

		Dim oRange
		oRange = Sheet.getCellRangeByPosition(0,0,10,0)

		oRange.merge(True)
		orange.charheight = 14
		orange.charweight = 150
		sheet.getcellbyposition(0,0).setstring StrConv(Format(datSingleDay, "dddd, "), 3) & Format(datSingleDay, "dd mmmm yyyy")

exit for 'I PUT THIS IN SO IT WILL EXIT AFTER THE FIRST DAY
	Next



End Sub

[Lupp]

(Deleted after a second look.)

[penguinoid]

It works! Thanks, JeJe!

[JeJe]

Looking at LibreOffice I see the sheet limit is much higher and it would be possible to have it all in one document using that instead of OO.