+ Reply to Thread
Results 1 to 7 of 7

defined name problem in vba

  1. #1
    Forum Contributor
    Join Date
    12-25-2012
    Location
    gent,belgium
    MS-Off Ver
    Excel 2021
    Posts
    146

    defined name problem in vba

    Hi

    Normaly when you defind a name
    then the name getal have a reference to for example =Blad2!$A$1:$A$3

    So in vba i can get the value of the second rouw lik this.

    Please Login or Register  to view this content.
    But that workt in a normal sub. But when i do the same in the vba code under the sheet then i have a error.
    the methode_range form object_worksheet is faild
    Why ?

    A person give me the folwoing
    Please Login or Register  to view this content.
    What is the use of [ in vba and { in worksheet formula's
    Where can i find more of it ?
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: defined name problem in vba

    You need to fully reference the range with the sheet name.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (22 H2) 64 Bit
    Posts
    1,133

    Re: defined name problem in vba

    Or, as an alternative:

    Please Login or Register  to view this content.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: defined name problem in vba

    ..and just another suggestion

    Instead of using the sheet tab name always use the VBA sheet CODE name.

    i.e. instead of Sheets("blad2").Range("getal").Cells(2)
    in your example

    Blad2.Range("getal").Cells(2)

    Tab names are to easily changed and a macro will fail when that happens.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: defined name problem in vba

    Typicaly Names are scoped to the Workbook. I also don't trust the implied Evaluate of the bracket notation []

    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  6. #6
    Forum Contributor
    Join Date
    12-25-2012
    Location
    gent,belgium
    MS-Off Ver
    Excel 2021
    Posts
    146

    Re: defined name problem in vba

    @ Richard Buttrey

    But what when the program is on a office and windows in other langue

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: defined name problem in vba

    Quote Originally Posted by glda19 View Post
    @ Richard Buttrey

    But what when the program is on a office and windows in other langue
    Well obviously you need to use the words from the natural language that applies to your version of excel.
    The advice is still the same. Don't use the sheet tab name use the VBA Sheet Code name. i.e. in the VBA Project Window for the workbook in question where you see he sheet objects shown as

    Aaaa1(Aaaa1)

    Use Aaaa1.Range("Some_range"), not Sheets("Aaaaa1").Range("Some_range")

+ 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. [SOLVED] Run Time 1004 - App Defined or Object defined error with String defined path structure
    By sarails in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-11-2012, 09:38 PM
  2. Apparent Defined Name Problem
    By Huhenyo in forum Excel General
    Replies: 3
    Last Post: 02-11-2012, 04:49 AM
  3. Problem using formulas within macro + problem with defined name.
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-11-2011, 04:39 AM
  4. defined Code Problem
    By need in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-02-2010, 02:26 AM
  5. defined name problem
    By arthas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-21-2009, 01:55 AM
  6. problem with user defined function
    By panjo in forum Excel General
    Replies: 3
    Last Post: 06-16-2006, 06:25 AM
  7. defined names problem
    By scott in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-05-2006, 05:40 PM
  8. [SOLVED] Defined range problem
    By Pat in forum Excel General
    Replies: 8
    Last Post: 01-17-2005, 08:06 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