Login required to started new threads

Login required to post replies

Excel VBA and creating an Array
Quote | Reply
First I’ve never dealt with arrays before. So there is that issue to start

I have a vba script that cycles through the data and create worksheet tabs as it processes. The tab names are variables that are different each time the script is ran.

Right now I am formatting / adding formulas to each sheet as the script runs. I would like to create all the tabs and then select the tabs as a group. Then make the tabs pretty as a set. This should be a time savings for the script.

I can record the code, but I cannot figure out how to define/redefine the array on the fly.

When I record I get this line:
Sheets(Array(“EXP20010501”, “EXP20010578”, “EXP20011029”)).Select

So how do I code the creation of the array on the fly I mean as I create each new tab, redefine the array to include that new tab. When the script is through creating the tabs, then I use the list of tab names to select all of the necessary tabs



Thanks Mike
Quote Reply
Re: Excel VBA and creating an Array [TooSlow] [ In reply to ]
Quote | Reply
I've always been lucky using stackoverflow for coding questions. If you aren't married doing it in VBA/excel, I've done something similar in python with the packages pandas(dataframes) and openpyxl (read/write/modify excel sheets). Learning those packages in python were pretty intuitive for someone with basic coding experience.
Quote Reply
Re: Excel VBA and creating an Array [TooSlow] [ In reply to ]
Quote | Reply
It's been ages since I've done VBA work but from the sound of it your logic should be something like putting a variable for the number of tabs in the creation of the array so that you have the length of the array correct when it's created. Then use another variable that holds the name of each cell of the array to be able to manipulate it in a loop.

I would second the googling of your problem to find similar solutions for examples.
Quote Reply
Re: Excel VBA and creating an Array [TooSlow] [ In reply to ]
Quote | Reply
This code should select all visible worksheets:

Sub SelectSheets() Dim myArray() As Variant Dim i As Integer For i = 1 To Sheets.Count ReDim Preserve myArray(i - 1) myArray(i - 1) = i Next i Sheets(myArray).Select End Sub
Quote Reply
Re: Excel VBA and creating an Array [efernand] [ In reply to ]
Quote | Reply
efernand wrote:
This code should select all visible worksheets:

Sub SelectSheets()
Dim myArray() As Variant
Dim i As Integer

For i = 1 To Sheets.Count

ReDim Preserve myArray(i - 1)
myArray(i - 1) = i

Next i

Sheets(myArray).Select

End Sub

So that gave me what I needed. The code below worked to select only the visible sheets.
I think I will be able to use the else portion. I will update the array with the variable that I am using to name the sheets as I create the sheets. I will place those two line right after I establish the new sheet name

Again thanks.

Sub SelectSheets()

Dim myArray() as variant
Dim i as string

Cnt = 1

For i = 1 to Sheets.Count
If Sheets(i).Visible = False Then
Cnt=cnt+1
Else
ReDim Preserve myArray(i - cnt)

myArray( i - cnt) = Sheets(i).Name
End if

Next i

Sheets(myArray).Select

End sub
Quote Reply
Re: Excel VBA and creating an Array [King_of_QZ] [ In reply to ]
Quote | Reply
King_of_QZ wrote:
I've always been lucky using stackoverflow for coding questions. If you aren't married doing it in VBA/excel, I've done something similar in python with the packages pandas(dataframes) and openpyxl (read/write/modify excel sheets). Learning those packages in python were pretty intuitive for someone with basic coding experience.

Yes I looked over at that web site. My problem, and I know it’s me, but when someone asks a question like this they tend to provide a hundred lines of code.

For me to learn best, I usually need all of the distraction removed from the answer. The code provided here was stripped down to the basics and therefore was easy for me to understand. Which for me was the two lines in my “else” section of my if statement.
Quote Reply