How to Inserts Sheets Using Array in Macros_Calc Spreadsheet?

English
, ,
#1

How to Insert Sheets Using Array in BASIC Macros ? I got error message
In Calc Spread Sheets - “Sheet1” is default , After the “Sheet1” , I want to Insert New Sheets Names are “East”, “West”, “North”, “South”.
How to Insert the Sheets Using Macros ?

Private Sub InsertSheets
		Dim Doc As Object, oSheets As Object
		Dim InsertingSheets("East", "West", "North", "South")
		
		Doc = ThisComponent 
		oSheets = Doc.Sheets
			
			For i = 0 to 3
  				oSheets.insertNewByName(InsertingSheets(i) , oSheets.Count )
 			next i
 			
End Sub

image

#2

Which programming language is that? Try this Dim Statement article in the ever-underrated help.

#3

LibreOffice BASIC Macro

#4

Hi, in Calc Spreadsheet “Sheet1” is default I want to inserts New Sheets.
Names Of the New Sheets are “East”, “West”, “North”, “South”.
How to Insert the Sheets Using Macros ?

Private Sub InsertSheets
		Dim Doc As Object, oSheets As Object
		Dim Array(East, West, North, South) As String
		
		Doc = ThisComponent 
		oSheets = Doc.Sheets
			
			For i = 0 to 3
  				oSheets.insertNewByName(Array(i),oSheets.Count)
 			next i			
End Sub

Still getting Error message

#5

If you were to ask “What is the correct way to describe an array of names for new sheets?” then the answer would be “Use the Array() function”:

Dim InsertingSheets As Variant
InsertingSheets = Array("East", "West", "North", "South")

But again you are asking the wrong thing…

1 Like
#6

Yes,

Private Sub InsertSheets
		Dim Doc As Object, oSheets As Object
		Dim Array(East, West, North, South) As String
		
		Doc = ThisComponent 
		oSheets = Doc.Sheets
			
			For i = 0 to 3
  				oSheets.insertNewByName(Array(i),oSheets.Count)
 			next i 			
End Sub

Still getting Error message

#7

Just compare
Dim Array(East, West, North, South) As String
and
InsertingSheets = Array("East", "West", "North", "South")

#8

Hi First One - In your code Successfully Insert Sheets.

Private Sub InsertSheets_Method_1
		Dim Doc As Object, oSheets As Object
		Dim InsertingSheets As Variant
		
		Doc = ThisComponent 
		oSheets = Doc.Sheets
		InsertingSheets = Array("East", "West", "North", "South")
		
			For i = 0 to 3
  				oSheets.insertNewByName(InsertingSheets(i),oSheets.Count)
 			next i		
End Sub

image

But, Second Code got error message

Private Sub InsertSheets_Method_2
		Dim Doc As Object, oSheets As Object
		Dim Array(East, West, North, South) As String
		
		Doc = ThisComponent 
		oSheets = Doc.Sheets
		InsertingSheets = Array("East", "West", "North", "South")
		
			For i = 0 to 3
  				oSheets.insertNewByName(InsertingSheets(i),oSheets.Count)
 			next i		
End Sub

Screen Shot of Error message

image

#9

Put yourself in BASIC’s shoes and try to explain the line Dim Array(East, West, North, South) As String in ordinary human words. You won’t succeed. Basic doesn’t do this either. And he’s trying to talk about it.

#10

Here is the screen shot of Error line 37
oSheets.insertNewByName(InsertingSheets(i),oSheets.Count)

image

Did you try get the same error message ?

#11

Once again: remove the stupid line Dim Array() from the code

1 Like
#12

Are you trolling? The string

Dim Array(East, West, North, South) As String

is WRONG. It is NOT a valid Basic array declaration. It is incorrect. Bad. Invalid. It results in the error. Do not do that, and do read the documentation. What else should be told to you?

1 Like
#13

Hi @mikekaganski, @JohnSUN … Thanks…
As JohnSUN Wrote Code … I just Consolidated the Macro Code.
This is for Beginners to get Knowledge Including Me…

Method_1

Sub InsertSheets_Method_1
      ' Declaring ....Dim InsertingSheets

		Dim Doc As Object, oSheets As Object
		Dim InsertingSheets As Variant
		
		Doc = ThisComponent 
		oSheets = Doc.Sheets
		InsertingSheets = Array("East", "West", "North", "South")
		
			For i = 0 to 3
  				oSheets.insertNewByName(InsertingSheets(i),oSheets.Count)
 			next i			
End Sub

Method_2

Sub InsertSheets_Method_2
       'Without Declaring ....Dim InsertingSheets 

		Dim Doc As Object, oSheets As Object
		Doc = ThisComponent 
		oSheets = Doc.Sheets
		InsertingSheets = Array("East", "West", "North", "South")
		
			For i = 0 to 3
  				oSheets.insertNewByName(InsertingSheets(i),oSheets.Count)
 			next i		
End Sub

Methdod_3

Sub InsertSheets_Sheet2_Sheet3_LikeThat
       ' Insert Sheet2, Sheet3, Sheet3, Sheet4 ... to Sheet10

		 sheets = thisComponent.Sheets
		 For i = 2 to 10
		  	 '   sheets.insertNewByName("anyName" & i , sheets.Count )
		  	 sheets.insertNewByName("Sheet" & i , sheets.Count )
		 Next i
End Sub
#14

beat_deadhorse

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.