-
Mar 8th, 2018, 06:41 AM
#1
Thread Starter
New Member
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.
-
Mar 8th, 2018, 08:29 AM
#2
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
-
Mar 8th, 2018, 09:00 AM
#3
Thread Starter
New Member
Re: Function input is string, then search that string ... IndexOf not working
Originally Posted by techgnome
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.
-
Mar 8th, 2018, 09:09 AM
#4
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
-
Mar 8th, 2018, 09:16 AM
#5
Thread Starter
New Member
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.
-
Mar 8th, 2018, 09:32 AM
#6
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?
-
Mar 8th, 2018, 09:36 AM
#7
Thread Starter
New Member
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
-
Mar 8th, 2018, 09:49 AM
#8
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.
-
Mar 8th, 2018, 10:07 AM
#9
Thread Starter
New Member
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.
-
Mar 8th, 2018, 10:58 AM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|