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 = ???
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!)