Results 1 to 10 of 10

Thread: Function input is string, then search that string ... IndexOf not working (Excel2010)

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Function input is string, then search that string ... IndexOf not working (Excel2010)

    Hi, I have generated a function that is used in a cell in excel. The input to the function is called mission and its just text written into a cell (in excel).

    Code:
    Function test(mission As String)
    
      index_x = mission.IndexOf("X")
      index_t = mission.IndexOf("T")
    
      if index_x > index_t Then
         test = Range("E1")
      Else
         test = Range("E2")
      End If
    
    End Function
    But it does not get past index_x=mission.IndexOf("X")

    Ive tried aswell as a multitude of different code lines
    Code:
    Function test(mission)
    index_x = mission.text.IndexOf("X")
    The cell defiantly contains the letter X! Please help
    Last edited by NateKJ; Mar 8th, 2018 at 10:11 AM. Reason: Been told this is VB for Applications.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Function input is string, then search that string ... IndexOf not working

    1) You need to tell the function what it will return... an integer in this case:
    Function test(mission As String)
    2) The preferred method for returning a value from a function is the Return keyword:
    Return index 'instead of test=index
    3) You don't really need to put the string into a temp var, nor the index either, you can return it right from the IndexOf:
    Code:
    Function Test(Mission As String) As Integer
      Return Mission.IndexOf("X")
    End Function
    4) Lastly unless this is an exercise in learning to write functions, things like this aren't usually written this way. Conventional wisdom would have the IndexOf check inline with where it needs to be, not factored out into a one-line function like this.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Function input is string, then search that string ... IndexOf not working

    Quote Originally Posted by techgnome View Post
    1) You need to tell the function what it will return... an integer in this case:
    Function test(mission As String)
    2) The preferred method for returning a value from a function is the Return keyword:
    Return index 'instead of test=index
    3) You don't really need to put the string into a temp var, nor the index either, you can return it right from the IndexOf:
    Code:
    Function Test(Mission As String) As Integer
      Return Mission.IndexOf("X")
    End Function
    4) Lastly unless this is an exercise in learning to write functions, things like this aren't usually written this way. Conventional wisdom would have the IndexOf check inline with where it needs to be, not factored out into a one-line function like this.

    -tg
    Hi, Thanks for the reply. The index is not the output of the cell, it was written like that to show the issue I am having. The input to the function, mission needs to be searched twice - One for the letter X and the other T. I need to then write some logic If statements for the indexs.

    Code:
    Function test(mission As String)
    
    index_x = mission.IndexOf("X")
    index_t = mission.IndexOf("T")
    
    if index_x > index_t Then
      test = Range("E1")
    Else
      test = Range("E2")
    End If
    
    End function
    But it wont go past line 2.

    I've edited my first post to reflect what I need, perhaps I didn't explain correctly.
    Last edited by NateKJ; Mar 8th, 2018 at 09:07 AM.

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: Function input is string, then search that string ... IndexOf not working

    Well, I'm assuming that when it "didn't go past line 2" it gave you an error, right? And it would be????
    Couple things jump out at me: you're still not returning the proper type on your function. And 2) you're not defining your variables.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Function input is string, then search that string ... IndexOf not working

    It says "Compile Error: Invalid Qualifier" and highlights mission in line 2 .... the first line of code in the function.

    Ive added
    Code:
    Dim index_x As Integer
    Dim index_t As Integer
    still gets same error

    Ive also added

    Code:
    Function test(mission As String) As Range
    Same issue
    Last edited by NateKJ; Mar 8th, 2018 at 09:22 AM.

  6. #6
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Function input is string, then search that string ... IndexOf not working

    Could you clarify for me if this is Visual Basic .NET using Visual Studios 2017 or if this is Visual Basic for Applications using Excel?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Function input is string, then search that string ... IndexOf not working

    Hi, It says Visual basic for Applications and Im using it from Excel

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,711

    Re: Function input is string, then search that string ... IndexOf not working

    OK, then I moved the thread from the Visual Basic .NET forum to the Office Development forum. You may want to edit your original post, go into advanced reply, and change the prefix of the title to include the version of Microsoft Office that you're using.
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    New Member
    Join Date
    Mar 2018
    Posts
    5

    Re: Function input is string, then search that string ... IndexOf not working

    So I found the solution
    VB Net is IndexOF
    VBA is InStr

    This is why I dislike Microsoft Visual Basic, its too fiddly. I much prefer python and MATLAB.
    Last edited by NateKJ; Mar 8th, 2018 at 10:16 AM.

  10. #10
    Sinecure devotee
    Join Date
    Aug 2013
    Location
    Southern Tier NY
    Posts
    6,582

    Re: Function input is string, then search that string ... IndexOf not working

    If you're using VBA or VB6 and earlier, then you should use that language and references.

    VB.Net is a different language, based on the original Visual Basic and using the same name.
    Unfortunately, since BASIC is such an old language, there has always been the question of whose version of BASIC you were using because there has always been differences in how things were done once you're past the very core key words.
    Even all the various versions of Microsoft based BASICS for the various machines, AppleII+, Commodore-64, Aim, Kim, etc... were tailored for the specific machines when it came to graphics and I/O, etc..

    In the case of .Net, it is a large set of libraries with classes and types that a number of languages can use, such as C#, F#, C++, and the version of Visual Basic adapted to integrate the .Net library usage with the language.

    Of course, since InStr is part of the original Visual Basic language (and earlier actually), it is still there as part of the VB.Net version of Visual Basic as part of Visual Basic, not part of the .Net libraries.

    If you choose to use VB.Net, then the preference is you use the common .Net libraries methods instead of the Visual Basic ones so that your code is easier to port to the other languages, C# in particular, that use the .Net libraries.

    Visual Basic for Applications (VBA) is its own version(s) of the legacy Visual Basic languages, which shares its core with VB4 to VB6. (1994 to 1998).
    You probably don't realize that each application, Word, Excel, etc... before around 1993 to 1994 had there own unique built-in scripting language.
    There was a bit of gnashing of teeth when Microsoft announced that it was replacing the various scripting languages in its applications with a common scripting language based on Visual Basic, calling it VBA. Many who were already competent in the existing scripting languages wanted nothing to do with BASIC, Visual or otherwise. But it came to pass, and VBA has been integrated into other applications as well, not just from Microsoft.

    You're right, that many newer languages benefit from not having been invented in the "wild west" days of the personal computer explosion where everybody was rushing to design and sell unique hardware and software, so you had a proliferation of unique incompatible methods of doing things. Now, commonality and cross platform interoperability is much more important, and that effects newer languages such as python.

    The main mistake is Microsoft using the name Visual Basic in (primarily) three different contexts (legacy Visual Basic (VB4 to VB6), Visual Basic for Applications (VBA), and Visual Basic for .Net (they stopped using the VB.Net term for whatever reason which adds to the confusion).
    You need to be aware of what context your use of the name Visual Basic is and pretty much treat it as a particular language, not synonymous with other uses of the name Visual Basic in another context.

    In my mind, it has always been VB, VBA and VB.Net as three different languages that share various amounts of the Visual Basic language (which I associate with VB). It has always been Visual Basic (VB), and the two other animals (VBA and VB.net).
    Last edited by passel; Mar 8th, 2018 at 11:10 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width