Results 1 to 5 of 5

Using Excel functions within VBA to define string variables

This is a discussion on Using Excel functions within VBA to define string variables within the Excel Questions forums, part of the Question Forums category; I am trying to define two variables for use in concatened instructions. The variables are the 1st 3 letters of ...

  1. #1
    New Member
    Join Date
    Aug 2005
    Location
    Fort Collins, CO
    Posts
    33

    Default Using Excel functions within VBA to define string variables

    I am trying to define two variables for use in concatened instructions. The variables are the 1st 3 letters of the worksheet and the overall worksheet name. The code is bombing at the first SET command when I try to define the first variable:

    Code:
        Dim dn As String
        Dim wsn As String
        Set dn = Mid(CELL("filename"), Find("]", CELL("filename")) + 1, 3)
        Set wsn = Mid(CELL("filename"), Find("]", CELL("filename")) + 1, 20)
    The error returned is:
    Compile Error:
    Sub or Function not defined.

    Note: the function =Mid(CELL("filename"), Find("]", CELL("filename")) + 1, 3) returns the first 3 letters of the name of the worksheet in question

    No doubt this is a simple syntax issue, but it escapes me.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,008

    Default

    String variables are not to be set.

    Dim dn As String
    Dim wsn As String
    dn = Left(ActiveSheet.Name, 3)
    wsn = ActiveSheet.Name


    The above suggestion will do what you had in mind, assuming it's the active sheet. If it's not the active sheet you had in mind, you should use the CodeName property instead of the sheet tab name.

  3. #3
    New Member
    Join Date
    Mar 2011
    Posts
    2

    Default Re: Using Excel functions within VBA to define string variables

    Hi,

    I am facing similar kind of problem when i use Find(":",sText) in my code like this.......

    sChunk = Mid(sText, Find(":", sText) + 1, Find(".", sText) - 1)


    the error refers to:
    Sub, Function, or Property not defined (Error 35)

    Can anyone explain it?

  4. #4
    New Member
    Join Date
    Mar 2011
    Posts
    2

    Default Re: Using Excel functions within VBA to define string variables

    I am using this syntax.....Find("S", sText, 1)

  5. #5
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,247

    Default Re: Using Excel functions within VBA to define string variables

    Try

    instr(sText,"S")
    HTH, Peter
    Please test any code on a copy of your workbook.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com