Insert Value Into Named Cell/Range in Excel : Visual Basic, Type Mismatch Error 13 and Error 2029 in Excel debugger

May 17, 2008 07:50am pdt

1. rorya 1,398,433
2. zorvek 957,911
3. saurabh726 734,863
4. jeverist 363,058
5. byundt 354,248
6. chris_bo... 335,112
7. ahammar 244,341
8. RobSampson 195,094
9. patrickab 193,594
10. webtubbs 164,200
11. matthews... 145,587
12. jpaulino 144,585
13. spattewar 123,964
14. weellio 111,530
15. nutsch 110,746

1. zorvek 8,262,402
2. byundt 7,096,962
3. rorya 5,092,714
4. jeverist 2,773,327
5. matthews... 2,680,035
6. patrickab 1,949,157
7. roos01 1,897,264
8. mvidas 1,504,853
9. webtubbs 1,260,592
10. fanpages 1,222,272
11. MalicUK 1,057,896
12. brettdj 1,042,576
13. saurabh726 828,442
14. mgh_mgha... 645,610
15. LoNeRaVeR9 602,049

03.21.2008 at 11:46PM PDT, ID: 23261162

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.9

Insert Value Into Named Cell/Range in Excel

Zones: Microsoft Excel Spreadsheet Software, .NET, Microsoft Visual Basic.Net

Tags: Visual Basic, Type Mismatch Error 13 and Error 2029 in Excel debugger

I'm trying to insert a value in an Excel 2007 worksheet cell from a VB.NET 2008 application. It looks like the value is being written into the cell correctly, however all the formulas that reference the cell are throwing #NAME? errors causing a type mismatch when looked at in the Excel debugger. The cell I'm trying to set the value for is a named cell/range so I'm not sure if this is causing any of my issues or not, I'm referencing it in my VB application using "A3" style references. Here's the code I'm using to try and set the value.

Any help is appreciated, I'm not sure what's wrong with this code.

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:

           
           
             Public Sub SetExcel(ByVal xlFileName As String, _
                         ByVal xlWorksheet As String, _
                         ByVal xlCellName As String, _
                         ByVal xlCellContents As String)
 
        On Error GoTo SetExcel_Err
 
        ' Create the Excel App Object 
        xlApp = CreateObject("Excel.Application")
 
        ' Create the Excel Workbook Object
        xlBook = xlApp.Workbooks.Open(xlFileName)
 
        ' Set the value of the Cell 
        xlBook.Worksheets(xlWorksheet).Range(xlCellName) = xlCellContents
        ' Force a recalculation
        xlApp.CalculateFull()
 
        ' Save changes and close the spreadsheet 
        xlBook.Save()
        xlBook.Close()
        xlApp.Quit()
        xlApp = Nothing
        xlBook = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
        Exit Sub
SetExcel_Err:
        MsgBox("SetExcel Error: " & Err.Number & "-" & Err.Description)
        Resume Next
 
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub

           
         

Start your free trial to view this solution

Question Stats

Zone: Software

Question Asked By: JjcampNR

Solution Provided By: harfang

Participating Experts: 3

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

03.22.2008 at 12:08AM PDT, ID: 21185059

Rank: Genius

zorvek:

Try setting the cell's Value property. Perhaps by setting the cell object you might be buggering the name referencing the cell. Are any other names in the workbook buggered? Did you check to see of the name in the workbook was deleted or just whacked in some way?

I'm assuming that the formulas referencing the cell are referencing the name versus the cell address.

Kevin

03.22.2008 at 09:20AM PDT, ID: 21186831

ildc:

Hi,

I see that xlCellContents is a string, maybe this can cause trouble in your other formulas.
Anyway, I'd assign it to the range().value or range().formula.

HTH,
iLDC

03.22.2008 at 12:38PM PDT, ID: 21187491

JjcampNR:

I tried using Range.Value and Range.Formula as well but this didn't help either. The contents of the cell are supposed to be a string, which is used in a number of ways throughout the workbook. I set the application to visible and it looks like, as the application runs, the value goes into the cell correctly but everything that runs off of that cell (using the name, not the cell address) is failing with $NAME? errors.

This workbook runs fine if I enter the value manually, so I'm really not sure what's going on. That cell is used as input to a number of different functions, which all refer to the name on the cell. The oddest thing is that if I kill the debugger, then open the worksheet regularly, the cell name shows up correctly (as it did when I viewed it while the debugger was running) and all the formulas are back to normal and they're working again. I suppose I could try entering the value, saving the sheet, closing it, then opening it again - but this just seems a bit unnecessary.

Thoughts?

03.22.2008 at 12:51PM PDT, ID: 21187530

JjcampNR:

OK - the close/open idea doesn't work. When the value is put into the cell, it triggers the workbook to recalculate and kick off the other formulas. Now, this all works fine when I enter the value manually, but I need to automate this - which doesn't work with the code above. If anyone can help, I'd greatly appreciate it.

03.22.2008 at 12:53PM PDT, ID: 21187536

JjcampNR:

Also, just as an FYI - I've changed back to using Range.Value as I had coded it originally (I posted using just Range, but I agree that Range.Value is the right way to go).

03.22.2008 at 08:46PM PDT, ID: 21188592

Rank: Guru

harfang:

I was unable to reproduce the bug in Excel 2003, using any combination of direct cell reference, named cell (both worksheet and workbook names), string or numeric, using .Formula, .Value, or the default property. (I append my last test below.)

To stretch it, I even used the *content* of the cell as label in a formula, by allowing "labels in formulas", and these formulas were correctly updated as well.

What steps do you need to take after the bug to re-activate your formulas showing #NAME? Recalculating? Rewriting a value manually? Rewriting any one of the formulas?

(°v°)

                       
                                    With GetObject("Book1.xls")
        .Names("test").RefersToRange = "new string"
        .Windows(1).Visible = True
        .Close True
    End With

Open in New Window

03.23.2008 at 09:51AM PDT, ID: 21190050

JjcampNR:

If seems that if I end the debugger, stop my program, and open the Excel sheet manually, it all comes back and the formulas show up correctly (I make no changes to the cell, I simply open the sheet through Excel). I'm not sure if this is an Excel 2007 bug or not - unfortunately I don't have a system with Excel 2003 to test on right now. I'm getting the issue in my spreadsheet isolated and I'll post that up shortly so you can take a look at what's going on more easily. Thanks for the help, it's greatly appreciated.

03.23.2008 at 09:19PM PDT, ID: 21191929

JjcampNR:

Alright, through the process of narrowing down the problem code it seems that I've got a bit more info on the cause of the error - and it's apparently not how I'm putting the value into the cells. The formulas that use the cell I've been having trouble with are part of a plug-in package and it looks like it's how Excel is referencing the functions that might be the problem.

For example when I put some of the plug-in code into a module in my workbook, it shows up in the formula bar as I'd expect (this one is an array entered formula), such as:
{=AccessNamedRange(param1, param2, NamedRange, param3, param4)}

However, when I look at the reference to the plug-in based formulas, they show up as:
{='C:\Program Files\Excel Add-In\Add-In_Functions.xla'!AccessNamedRange(param1, param2, NamedRange, param3, param4)}

When I evaluate the calculation steps I get a #REF! on the Add-In based functions, which is causing my #NAME? errors, but this doesn't happen on the one that's in a local module (they are exactly the same code, I just put one locally for testing).

The formulas are not being accessed, input, referenced, or otherwise touched by the application I'm working on - it's only the named range (one cell) that the application interacts with. Any idea what's happening and why having the code as a plug-in is a problem? I really don't want to port all of the functions into local modules since that'd take a huge amount of time and I'm not even sure if that's going to be realistic.

03.23.2008 at 09:29PM PDT, ID: 21191951

JjcampNR:

Sorry for the multiple responses, but as I've been troubleshooting I've found more weirdness...

When my application opens Excel, none of the add-in functions are available and in the VBA window the Plug-In project and all of its modules do not show up at all. However, if I open the sheet manually, everything shows up just fine. The location I'm opening the file from is trusted, so i do not receive any prompts to enable macros in the worksheet, they're automatically enabled.

I'm at a loss - any ideas?

03.23.2008 at 09:51PM PDT, ID: 21192000

Rank: Guru

harfang:

That makes sense. The instance of Excel you are using does not load the plug-ins. The one major difference between your code an manually opening a sheet is that you are using an automated instance.

For example, if you want to use the currently open user controlled instance (provided there is one), you can find it like this:

' Get the current Excel App Object
xlApp = GetObject(, "Excel.Application")

But that has other problems (for example if that instance is in print preview or currently editing a cell, it becomes totally unresponsive to automation).

Perhaps we can try this to trick Excel:

' Create the Excel App Object
xlApp = CreateObject("Excel.Application")
xlApp.UserControl = True

Or you can try to check the addins

Debug.Print xlApp.AddIns.Count

Or you can manually open your plug-in before opening your workbook...

But to tell the truth, I no longer understand your problem. If I read you correctly, there is only a problem *while* you are updating the cell, but not when you later open the workbook. And you do save and close it at this stage. Or did I miss something?

(°v°)

03.24.2008 at 07:23PM PDT, ID: 21199101

JjcampNR:

I can completely understand the confusion, the problem has morphed a bit since I've continued troubleshooting and uncovered additional info. The problem now is that when using my application (the code above) to open my Excel sheet, the plug-in does not work correctly, however it does work correctly when I manually open the sheet (as you mentioned, this makes more sense since the code to add the value to the cell is correct).

It seems that I just need to find a way to force Excel to correctly load the plug-ins before I get to the point where I change the value of the cell. I tried the method you suggested above, however this didn't correct the issue and I still got the #NAME? errors. The debug code produced a value of "8" however when I looked in the options pane, I only see 6 active Excel plug-ins listed (I didn't look at the COM or other add-ins, so there may be an additional one or two there). Also, it's odd because if I check the add-ins section on the instance my application opens, the plug-in that's not working shows up in the list of active add-ins. Is there code I can use to specifically load a plug-in after I create an instance of the Excel application? There's nothing else I can think of that would be causing the issue except the plug-in not properly loading. Thanks again for the help!

03.24.2008 at 07:56PM PDT, ID: 21199208

Rank: Guru

harfang:

I must say I'm at a loss. Perhaps you can just leave the error, and add an automatic recalc when opening your workbook? That way you do save the correct value in the cell, and leave the problem of the plug-in for when the derived expressions are really needed.

Or, one last shot, try to open the plug-in manually. Perhaps it's somehow only in the list of "things to do", but not really activated.

(°v°)

                       
                                        ' Create the Excel App Object 
        xlApp = CreateObject("Excel.Application")
 
        ' Load plug-in
        xlApp.Workbooks.Open "C:\Program Files\Excel Add-In\Add-In_Functions.xla"
 
        ' Create the Excel Workbook Object
        xlBook = xlApp.Workbooks.Open(xlFileName)

Open in New Window

03.24.2008 at 10:56PM PDT, ID: 21199770

JjcampNR:

We were on the right track - I figured it out in tonight's "bash my head against the wall until I hallucinate and visualize an answer" troubleshooting session. I needed to access the add-in using a Call statement in order to get the functions to load into the open Excel book, simply opening the XLA as a workbook or add-in wouldn't do it. I ended up putting in the call statement in the line below creating the workbook object. I've pasted in the working code so you can see what ended up doing it for me.

I'll close the question once I have a bit more time to test tomorrow - if everything is good in my testing I'll award you the points since you were helpful and quick to respond.

                       
                          
                             
                             
                                Public Sub SetExcel(ByVal xlFileName As String, _
                         ByVal xlWorksheet As String, _
                         ByVal xlCellName As String, _
                         ByVal xlCellContents As String)
 
        On Error GoTo SetExcel_Err
 
        ' Create the Excel App Object 
        xlApp = CreateObject("Excel.Application")
 
        ' Create the Excel Workbook Object
        xlBook = xlApp.Workbooks.Open(xlFileName)
 
'**********************************************************************
        Call xlApp.Workbooks.Open("C:\Program Files\Add-In\Add-In.xla")
'**********************************************************************
        
' Set the value of the Cell 
        xlBook.Worksheets(xlWorksheet).Range(xlCellName).Value = xlCellContents
        ' Force a recalculation
        xlApp.CalculateFull()
 
        ' Save changes and close the spreadsheet 
        xlBook.Save()
        xlBook.Close()
        xlApp.Quit()
        xlApp = Nothing
        xlBook = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
        Exit Sub
SetExcel_Err:
        MsgBox("SetExcel Error: " & Err.Number & "-" & Err.Description)
        Resume Next
 
        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Sub
                             
                          

                    

Open in New Window

03.25.2008 at 03:28AM PDT, ID: 21200633

Rank: Guru

harfang:

> "bash my head against the wall until I hallucinate [...]"

"... and see the pink Microsoft monster under the desk!" That would be my reaction to any significant difference between "xlApp.Workbooks.Open" and "*Call* xlApp.Workbooks.Open"!

That would mean if you do not "Call", you don't care about the result and Excel can simply put the instruction into a waiting list? I mean it's bad enough to show the correct count of AddIns, but not make them available. Well that I understand in a way, if you have tons of them, you probably don't want them in your automated version (you don't need the Euro converter, the Adobe add-in, or the solver) but as soon as you use any of them, they should load (think about the analysis toolpack and its additional functions).

Anyway, I'm glad you found a solution: I was at the end of my tricks.

(°v°)

Accepted Solution

03.25.2008 at 06:56PM PDT, ID: 21208226

JjcampNR:

It was also frustrating that when I added code to explicitally install and enable the add-iin no errors were thrown saying that it was already loaded and/or enabled but it STILL didn't work. Oh well, my head bruises will go away in a few days and I won't soon forget to use "Call" in the future. I hope this at least keeps you from having to injure yourself if you run across this down the line. Thanks for the help, case closed for this issue.

harfang

03.26.2008 at 05:53AM PDT, ID: 21211021

Thanks for points and grade, especially as you finally found the trick yourself! I won't forget this one either...
Success with your project!
(°v°)

20080236-EE-VQP-29 / EE_QW_2_20070628