+ Reply to Thread
Results 1 to 4 of 4

VBA - Evaluate returning #Value although working in a test sheet

  1. #1
    Registered User
    Join Date
    03-31-2017
    Location
    Paris, France
    MS-Off Ver
    2007
    Posts
    2

    VBA - Evaluate returning #Value although working in a test sheet

    Hi,
    A formula with Evaluate works fine in a test workbook.
    All results are good.
    Exactly the same code returns #Value in the workbook I should use (that contains other macros).
    The error code is 2029.
    In addition, when I only put in the sheet the formula resulting from the code (either when debugging or directly in the code), for instance
    Please Login or Register  to view this content.
    , the result is correct if I do Ctrl+Shft+Enter in the cell where the formula is .

    As a consequence, what I understand is that the formula is not evaluated by Application.Evaluate.
    Any hints ?
    The following is part of the code
    Please Login or Register  to view this content.
    Any help would be appreciated
    Thanks in advance
    Dom

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: VBA - Evaluate returning #Value although working in a test sheet

    Dom,

    It is possible that your formula needs to be in RC or A1 form for VBA to properly evaluate it.

    You could also try using this to evaluate the array formula.

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: VBA - Evaluate returning #Value although working in a test sheet

    Hi,

    It's also possible that the external address includes the full path, which could make your formula longer than the 255 character maximum supported by Evaluate.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  4. #4
    Registered User
    Join Date
    03-31-2017
    Location
    Paris, France
    MS-Off Ver
    2007
    Posts
    2

    Re: VBA - Evaluate returning #Value although working in a test sheet

    Exactly !!!!
    Real thanks to both of you guys !!
    This morning, I tried Bernie's solution. It failed but when I tried to understand why, I found on this page that FormulaArray doesn't work with more than 255 characters.
    So I renamedmy filename with only 2 letters and it ...worked !!!
    Thanks a lot !
    Dom

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Nested Application.Evaluate not working
    By kaktus018 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 05-22-2016, 04:18 PM
  2. evaluate function not working
    By stephme55 in forum Excel General
    Replies: 1
    Last Post: 04-06-2016, 01:17 PM
  3. [SOLVED] Evaluate in VBA not working
    By Platinum3x in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-25-2015, 11:41 AM
  4. Application.Evaluate not working - Array Formula
    By cmore in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-28-2015, 01:12 AM
  5. Excel Logic test returning incorrect responses
    By S Hoffmann in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-03-2015, 08:16 AM
  6. [SOLVED] 'Evaluate' code not working
    By Woulter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2015, 01:09 AM
  7. VBA: using Evaluate with sumproduct function ( Date Comparison not working)
    By vidyuthrajesh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-22-2012, 05:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1