Sean Sean -4 years ago 159 Question

How to update RefersToR1C1 Excel named range in Visual Studio/VB

Latest edit: I've tried a ton of stuff, still can't figure this out. A more concise question is how I assign the variable. Per the first comment I got, I'm using VSTO.

Dim wksSheetVSTO As Worksheet = Globals.Factory.GetVstoObject(wsThisSheet)
Dim nrThisNamedRange As NamedRange = ???

Original Question:

I am brand new to Visual Studio/VB.Net but am an experienced VBA programmer. I have a very complex spreadsheet that uses Excel named ranges, and I can’t seem to figure out how I can manipulate them.

The existing spreadsheet uses Excel named ranges that essentially act like database tables. When a user adds a row, it adds a row to the named range. Since Excel doesn’t automatically extend the range itself, the VBA code re-defines the named range to include the new row by re-defining the RefersToR1C1 property of the named range. It works perfectly in VBA. (I can post the working VBA code if it helps.)

In Visual Studio/VB, I can’t figure out a way to assign a variable to the named range. The only documentation I can find talks about assigning a variable by creating a NEW named range, but I already have the named range. My work-around (which works, but looks bad to me) is below:

Dim rngDataStore As Excel.Range
Dim nrThisNamedRange As NamedRange

...<<stuff happening here>>...


' The next line is what I don't want.
nrThisNamedRange = wksSheetVSTO.Controls.AddNamedRange(wksSheetVSTO.Range("A1"), <<name>>)
nrThisNamedRange.RefersToR1C1 = <<new named range R1C1 here>>

I don’t want to just extend the working range (rngDataStore), I need to actually update the definition of the named range so that I can continue to reference and write to it. (This is doing what ctrl+F3 allows you to do within Excel.) I’ve tried looping through the Names collection, but other than using the ‘Controls.AddNamedRange’ I can’t figure out how to simply assign the nrThisNamedRange variable and update its RefersToR1C1 property.

Added: The above code doesn't always work, it's giving me an error that the named range already exists. (Which of course I know!)

Answer Source

For global named range:

Dim name = Globals.ThisWorkbook.Names("name")
Dim range = name.RefersToRange
name.RefersTo = range.Resize(range.Rows.Count + 1)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download