Excel Named Ranges

[conduirez]

Hope this helps someone.

I have been developing a workbook for a while now with multiple Bet Angel worksheet to do a days trading, initially on Betdaq for testing before using my main Betfair account. I realised how much Excel was being slowed down by the number of cell formulas I had added, and really did not need any to run until 15 minutes before the start time of the event.

I came up with a solution that would not only allow me to have one set of formulas that cover every worksheet, but would allow me to view the main items from any Bet Angel worksheet.

The solution simply is to use a Named Range for the Bet Angel worksheet, but this must be the same Named Range for each worksheet. The code below should be placed in a Module and a button placed on the data collection worksheet with assigned Macro SheetSelectNamedRange, by then placing number 4 say in cell "A1" and 35 in Cell "B1" the Named Range will be set to the worksheet 'Bet Angel (4)' and be 35 columns wide, the default number of columns is 31 if cell "B1"is blank, I could then Change cell "A1" to 3, press the button and the Named Range will now refer to 'Bet Angel (3)'

To get info and for cell formulas, use the cell formula Index Function such as =INDEX("apple", 1, 1) in a cell, which will always return the value from cell "A1" on whatever sheet the named range is referring to.

When I get a chance I will create a workbook and post it, unless someone else wishes to do it first.

Code: Select all

Sub SheetSelectNamedRange()
    Dim wksh As Worksheet
    Dim var_Worksheet_Number As Variant
    Dim var_Column_Number As Variant
        Set wksh = ThisWorkbook.ActiveSheet
        var_Worksheet_Number = wksh.Range("A1").Value
        var_Column_Number = wksh.Range("B1").Value
        Call MakeNamedRange(var_Worksheet_Number, var_Column_Number)
        Set wksh = Nothing
End Sub

Sub MakeNamedRange(ByVal int_Worksheet_Number As Variant, Optional ByVal int_Number_Of_Columns As Variant)
    Dim ws As Worksheet
    Dim wa As Worksheet
    Dim Input_Error As Boolean
    Dim nr As Name
    Dim ra As Range
    Dim bool_Name_Exists As Boolean
    Dim int_Total_Number_Of_Columns As Integer
    Dim str_Name_Of_Sheet As String
        Input_Error = False
        If VarType(int_Worksheet_Number) <> 2 Then Input_Error = True
        If int_Number_Of_Columns Is Nothing Then
            int_Number_Of_Columns = 31
        Else
            If VarType(int_Number_Of_Columns) <> 2 Then Input_Error = True
        End If
        If Input_Error = False Then
            If int_Worksheet_Number < 1 Or int_Worksheet_Number > 10 Then Input_Error = True
            If int_Number_Of_Columns < 31 Or int_Number_Of_Columns > 500 Then Input_Error = True
        End If
        If Input_Error = False Then
            Application.ScreenUpdating = False
            Set ws = ThisWorkbook.ActiveSheet
            If int_Worksheet_Number = 1 Then
                str_Name_Of_Sheet = "Bet Angel"
            Else
                str_Name_Of_Sheet = "Bet Angel (" & int_Worksheet_Number & ")"
            End If
            Set wa = ThisWorkbook.Sheets(str_Name_Of_Sheet)
            wa.Select
            Set ra = Range(Cells(1, 1), Cells(100, int_Number_Of_Columns))
            bool_Name_Exists = False
            For Each nr In ThisWorkbook.Names
                If "apple" = nr.Name Then bool_Name_Exists = True
            Next nr
            If bool_Name_Exists = False Then
                    ThisWorkbook.Names.Add Name:="apple", RefersTo:=ra
                Else
                    ThisWorkbook.Names("apple").RefersTo = ra
            End If
            ws.Select
            Set wa = Nothing
            Set ws = Nothing
            Application.ScreenUpdating = True
        End If
End Sub