Hi,
We are developing an Excel VSTO document-level addin.
One of the requirements of the addin is to generate a large number of very long named ranges. This is done by calling a VBA macro from the VSTO addin to create the ranges.
The VSTO addin is linked to the workbook through two custom properties.
The problem is that when there are a large number of very long named ranges (9000-10000), when we save the workbook, the custom properties disappear. So when the workbook is opened again, the addin does not start.
The problem is relatively easy to reproduce
) Open a blank workbook.
2) File -> Properties -> Custom
3) Add two properties as follows (note: I don't think it makes a difference what we call them but these are the names used for addins)
a) _AssemblyName, Value = *
b) _AssemblyLocation, Value = {2324342-sdfsdfdf-2323234}
4) Exit and Save workbook.
5) Open Workbook. You will probably get an error at this time about the Addin. Excel tries to use the properties created above to load a non-existent addin. Just click ok.
6) Check that properties are still there.
7) Add the following Macro and run it
Sub CreateNamedRanges()
Dim myName As String
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim rngCell As Range
Set rngCell = ActiveSheet.Range("A1", "A10000")
rngCell.Select
Dim cnt As Long
cnt = 0
For Each rngCell In Selection
With rngCell
myName = "AbcDEFxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & cnt
wb.Names.Add Name:=myName, RefersToR1C1:= _
"=R" & rngCell.Row & "C" & 12
End With
cnt = cnt + 1
Next rngCell
Range("A1").Select
End Sub
8) It takes about 30 seconds to run for 10000 ranges.
9) Once it completes, verify that the ranges are created.
10) Verify that the properties are still there - they should be.
11) Exit and Save.
12) Open the workbook again. In our case we do not receive the error message received in step 5.
13) Check the properties again. In our case the properties are gone.
We are using Windows XP SP2 with Excel 2003 Professional SP3.
We have tried to save the properties in a hidden cell when the workbook is first opened, and then rewrite them just before saving, but it seems that just after the save event and before closing, the properties are overwritten.
I don't think this is a VSTO issue as the problem seems to be reproducible with macros.
I would appreciate any ideas or help in debugging the issue.
Thanks
Chris
We are developing an Excel VSTO document-level addin.
One of the requirements of the addin is to generate a large number of very long named ranges. This is done by calling a VBA macro from the VSTO addin to create the ranges.
The VSTO addin is linked to the workbook through two custom properties.
The problem is that when there are a large number of very long named ranges (9000-10000), when we save the workbook, the custom properties disappear. So when the workbook is opened again, the addin does not start.
The problem is relatively easy to reproduce
) Open a blank workbook.
2) File -> Properties -> Custom
3) Add two properties as follows (note: I don't think it makes a difference what we call them but these are the names used for addins)
a) _AssemblyName, Value = *
b) _AssemblyLocation, Value = {2324342-sdfsdfdf-2323234}
4) Exit and Save workbook.
5) Open Workbook. You will probably get an error at this time about the Addin. Excel tries to use the properties created above to load a non-existent addin. Just click ok.
6) Check that properties are still there.
7) Add the following Macro and run it
Sub CreateNamedRanges()
Dim myName As String
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim rngCell As Range
Set rngCell = ActiveSheet.Range("A1", "A10000")
rngCell.Select
Dim cnt As Long
cnt = 0
For Each rngCell In Selection
With rngCell
myName = "AbcDEFxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" & cnt
wb.Names.Add Name:=myName, RefersToR1C1:= _
"=R" & rngCell.Row & "C" & 12
End With
cnt = cnt + 1
Next rngCell
Range("A1").Select
End Sub
8) It takes about 30 seconds to run for 10000 ranges.
9) Once it completes, verify that the ranges are created.
10) Verify that the properties are still there - they should be.
11) Exit and Save.
12) Open the workbook again. In our case we do not receive the error message received in step 5.
13) Check the properties again. In our case the properties are gone.
We are using Windows XP SP2 with Excel 2003 Professional SP3.
We have tried to save the properties in a hidden cell when the workbook is first opened, and then rewrite them just before saving, but it seems that just after the save event and before closing, the properties are overwritten.
I don't think this is a VSTO issue as the problem seems to be reproducible with macros.
I would appreciate any ideas or help in debugging the issue.
Thanks
Chris
