VBA Macro to create named ranges overwrites custom properties

chrdunne

New Member
Joined
Jul 5, 2010
Messages
2
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I don't have Excel 2003 where I am at the moment, but for what it's worth I can't reproduce your problem on Excel 2000.

When you say "You will probably get an error at this time about the Addin", why should that be? Excel knows nothing about those properties and I therfore got no error.
 

chrdunne

New Member
Joined
Jul 5, 2010
Messages
2
Thanks Andrew.

I'm not sure how much you know about VSTO addins. But it seems that when a document-level addin is associated with a workbook, the two mentioned properties are created. These seem to tell Excel where to find the addin and launch it.

Obviously with this sample there is no addin, so Excel complains and you get an error message that you can just ignore in this sample.

Regards
Chris
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,978
Messages
5,767,422
Members
425,411
Latest member
sarmientojayexce03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top