Excel VBA error when using dynamic ranges

Question

I am tryin to fill a combobox (dragged onto my worksheet) with the values of a dynamic range.

Normaly 'ThisWorkbook.Names("NAME_OF_RANGE").RefersToRange' works, but it fails on my dynamic range!

I have tried a different solution (see code below), but then the worksheetname is stripped from the reference (and thus the wrong data is being filled)

Public Sub FillCombobox(cboComboBox As ComboBox, sRange As String)
'Remember current selected item
Dim sSelectedItem As String
sSelectedItem = cboComboBox.Text

'Empty the combobox
cboComboBox.Clear
cboComboBox.AddItem "---pick one---"

'Get the data from the dynamic range
Dim oListFillRange As Range
'This line will throw an error:
Set oListFillRange = ThisWorkbook.Names(sRange).RefersToRange 'Does not work with a dynamic range!!!
''Set oListFillRange = Range(Application.Evaluate(ThisWorkbook.Names(sRange).RefersTo).Address) 'Works with dynamic ranges on the SAME SHEET (as the sheetname is stripped out!)

'Fill combobox
Dim oRange As Range
For Each oRange In oListFillRange
    cboComboBox.AddItem oRange.Value
Next oRange

'Set previous selected item
Dim i As Integer
For i = 0 To cboComboBox.ListCount - 1
    If cboComboBox.List(i) = sSelectedItem Then
        cboComboBox.ListIndex = i
        Exit for
    End If
Next i

If cboComboBox.ListIndex = -1 Then cboComboBox.ListIndex = 0
End Sub

So how do I get 'ThisWorkbook.Names("NAME_OF_RANGE").RefersToRange' to work with dynamic ranges?

Scott Holtzman · Accepted Answer · 2012-10-16 14:08:38Z

1

Refer to the range by the Range property.

Set oListFillRange = Sheets("mySheet").Range(sRange) 'where mySheet is the relevant sheet name

For some more information, the reason that RefersToRange does not work is because there is no range in the formula for dynamic ranges, so the excel properties cannot read the actual range. Therefore, referring to the range directly is the only way to go.

edited Oct 16, 2012 at 14:08

answered Oct 16, 2012 at 13:57

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Hamster_NL Over a year ago

Your suggestion for adding the sheet name works, Thanks! I rather use the 'global'method, so when I rename the sheet I don't have to worry about updating my VBA-code, but it seems like I have no choice here...

Scott Holtzman Over a year ago

Yes, now that I think about it, you need the sheet name no matter what. I will edit my answer to be more accurate.

Doug Glancy Over a year ago

I'm able to refer to a dynamic range using Name.RefersToRange. But your answer is more direct.

Scott Holtzman Over a year ago

@DougGlancy -> how so? It's been a while since I came across this issue, but I remember having the same problem where this approached solved it. I'd love to know another way to do it in case I need it in the future.

Doug Glancy Over a year ago

I don't know what else to add. With code similar to that in the post I can set a range variable to the RefersTo property of a dynamic Name at either the Workbook or Sheet level. Did you try it just now?

|

Collectives™ on Stack Overflow

Excel VBA error when using dynamic ranges

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related