Can VBA be used to do 'For Each' with Public Const?

[TechTank]

Probably a bit of a tough one to describe but I'll do my best:

I've got around 147 sub routines that do the same thing but with different URLs. Is there a way to use a loop to say 'Hey, for each URL in the PUBLIC_Declarations sub routine do this with it and then stop at the end'.

All 147 sub routines look like this with the only thing changing being the MyURL_URL and the Call Next_Subroutine:

Sub My_Subroutine_Web()

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Call Worksheet_Maintenance
(This just does some stuff with the worksheets ready for the data to be handled)

'URLs stored in URL_Variables Module
With ActiveSheet.QueryTables.Add(Connection:=MyURL_URL, Destination:=Range("$A$1"))
.Name = "MrExcel"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
         
    RawItemName = ActiveSheet.Range("A7").Value
    Result = Trim(Split(RawItemName, "-")(0))
    ItemName = Result

Rows("1:14").EntireRow.Delete

Call Data_Preparation
(This just trims and gets the data I want in the form I want)

Application.ScreenUpdating = True
    
Call Next_Subroutine

End Sub

The URL string comes from a sub routine called PUBLIC_Declarations and looks this:

Public Const MyURL_URL As String = "URL;http://mrexcel.com"
Public Const MyURL_URL As String = "URL;http://mrexcel.com"
Public Const MyURL_URL As String = "URL;http://mrexcel.com"

These all take quite a bit of time to set-up and run so my questions are:

1) Could I do a 'For Each URL in PUBLIC_Declarations DO This_Sub' and just have 1 subroutine that is used for each URL?
1a) If so does anyone know how I would do that or have some links?

2) Is this the most efficient way of doing these queries or is there a more efficient method I could use? I'm no expert and have had a lot of help from Mr Excel forum goers over the years to actually get this to work as it stands now.

Thanks for reading and sorry if it's not clear what I'm asking.

 

[shg]

You can't iterate through a set of constants.

You could put them into an array (or list them on a sheet) and iterate through that.

 

[Comfy]

You could do something like this:

Const allURLS = "URL1;URL2;URL3"
Public URLS As Variant


Sub YourMacro()
Dim URL As Variant


URLS = Split(allURLS, ";")


For Each URL In URLS
'Your code here
    Debug.Print URL
Next URL


End Sub

Store all you urls in one long string.
Use the split function to add each url to an array
Loop through each url in the array.

Although the Public Declaration wouldn't be needed if this was all in the same subroutine.

 

[TechTank]

Thanks Comfy for that bit of magic up there, I'l give this a go and see how I get on.

Sure does look a lot easier than what I've currently got going on.

 

[TechTank]

I'm getting a 'Line too long' error pop up when trying to add all the URLs (147 of them so far) to

Const allURLS = "

The only thing that changes in the URL is the last part. Not sure if I can do a variable for the first part of the URL and then "add" the end to it every time. Gonna have to look a bit further into this but thanks for the reply. At least I now have something to work with going forward and know it can be done.

 

[Comfy]

Const allURLS = "4062512;4062513;4062514"
Public URLS As Variant

Sub YourMacro()
Dim URL As Variant

URLS = Split(allURLS, ";")

For Each URL In URLS
'Your code here
    Debug.Print "www.mrExcel.com/.......#post"& URL
Next URL

End Sub

Or put the URLs into a "url" worksheet and loop through these. You can hide the worksheet should you wish.

 

[TechTank]

Thanks Comfy, working like a charm and so much easier to maintain.

Have a great weekend.