Function to assign variables to range

Question

I tried something like the code below.

Public currentplayer as range
Public opponentplayer as range

Set currentplayer = Cells.Find("CURRENTPLAYER", , , xlWhole).Offset(0, 1)
Set opponentplayer = Cells.Find("OPPONENTPLAYER", , , xlWhole).Offset(0, 1)

However, as more ranges get defined it becomes messier and tedious.

I tried to come up with something as follows:

Public variablename As Range

Function assigntovar(variablename, lookfor)
    Set variablename = Cells.Find(lookfor, , , xlWhole).Offset(0, 1)
End Function

Sub definevariables()
    Call assigntovar("currentplayer", "CURRENTPLAYER")
    Call assigntovar("opponentplayer", "OPPONENTPLAYER")
End Sub

Are there ways or methods that can be done or would I have to go with the original way?

Answer 1

You have to set the result of the function to reuse it (I renamed the function!)

Function getFieldForLabel(ws As Worksheet, label As String) As Range
    On Error Resume Next    'in case label not found
    Set getFieldForLabel = ws.Cells.Find(label, , , xlWhole).Offset(0, 1)
End Function

And then call it like this

Sub definevariables()
    
    Dim currentplayer As Range
    Set currentplayer = getFieldForLabel(ActiveSheet, "CURRENTPLAYER")
    
    Dim opponentplayer As Range
    Set opponentplayer = getFieldForLabel(ActiveSheet, "OPPONENTPLAYER")
    
End Sub

As you can see ActiveSheet is passed as well to the function. Just to be explicit on which sheet label should be searched. You can use sheetname as well like eg ThisWorkbook.Worksheets("Your sheetname")