none
Referencing a rangename with a constant as the RefersTo value RRS feed

  • Question

  • I have stored a rangename in a file with a constant (=TRUE) as it's RefersTo value, rather than refering to a cell address.

    This rangename is to be used as a check to identify it as a valid XL source file.

    ie I run a macro in a file which prompts the user to select a source file. If they don't select this file, the macro reports an error. 

       If they do select this file, then the macro continues.

    I'm not able to successfully reference this rangename when the valid file is open; I get a 438 error if I use a reference like

    checkvalue = wbkSource.range("CheckRange") 'where wbkSource is a workbook object to the opened file

    Is is possible to do this, or must I revert to using a rangename pointing to an actual cell(s)?

    Thanks.

    Thursday, October 16, 2014 11:57 PM

Answers

  • Range is not a property of the Workbook object. Moreover, CheckRange is not really a range, but a defined name.

    You can use

    checkvalue = wbkSource.Names("CheckRange").Value

    This will assign the string value "=TRUE" to checkvalue.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 17, 2014 12:50 AM
  • Thanks for the reply Hans. This solves my problem.

    As way of example,  I can apply this in two ways...

    Sub Test()
      
      'Rangename 'DataExtractFile contains =TRUE as it's ReferTo value
      
      Dim strCheck As String
      checkvalue = ThisWorkbook.Names("DataExtractFile").Value
      
      MsgBox strCheck
      
      Dim nmCheck As Name
      Set nmCheck = ThisWorkbook.Names("DataExtractFile")
      
      MsgBox nmCheck.Value
      
    End Sub


    In both cases, the MsgBox displays the text =TRUE.


    PJ

    Friday, October 17, 2014 4:57 AM

All replies

  • Range is not a property of the Workbook object. Moreover, CheckRange is not really a range, but a defined name.

    You can use

    checkvalue = wbkSource.Names("CheckRange").Value

    This will assign the string value "=TRUE" to checkvalue.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 17, 2014 12:50 AM
  • Thanks for the reply Hans. This solves my problem.

    As way of example,  I can apply this in two ways...

    Sub Test()
      
      'Rangename 'DataExtractFile contains =TRUE as it's ReferTo value
      
      Dim strCheck As String
      checkvalue = ThisWorkbook.Names("DataExtractFile").Value
      
      MsgBox strCheck
      
      Dim nmCheck As Name
      Set nmCheck = ThisWorkbook.Names("DataExtractFile")
      
      MsgBox nmCheck.Value
      
    End Sub


    In both cases, the MsgBox displays the text =TRUE.


    PJ

    Friday, October 17, 2014 4:57 AM