Mid() function

Excel 97 VBA- I can't get the Mid() function to work when used like Left() or Right(). e.g. variable = Mid("string", 3, 2) should return "ri", but I cant get it to work. Can anyone help me??? Thanks.

There isn't a whole lot more to it than that... you said that it should be returning "ri", but what are you getting back? Is the variable that you are loading defined as a string or a variant? Are you getting any kind of an error on this? Post some of the code and I will take a look at it for you. I am usually pretty good at Excel questions...

Are you typeing your code into a module or straight into the cell? It works for me if I put a Public Function in a module.


cell A1 has the word "string"
cell B1 has "=MyFunction(A1)"


'''''in module
Public Function MyFunction(anyval As String) As String
MyFunction = Mid(anyval, 3, 2)
End Function


Hope this helps,

Eric

I copied the following code from help and pasted it into a Module.

Sub help()

Dim MyString, FirstWord, LastWord, MidWords As String
MyString = "Mid Function Demo"
FirstWord = mid(MyString, 1, 3) 'Is supposed to return "Mid".

End Sub

Execution of this code generates the error-
"Compile Error"
"Wrong number of arguments or invalid property assignment."

I can't get it to run.

Okay, open up a form and add a command button named Command1. Just paste this code into the Form and see if it works...

Public Sub Command1_Click()

Dim MyString as String

MyString = Mid("DOES THIS WORK", 11,4)

MsgBox MyString, vbExclamation

End Sub

If this works, you should see the word WORK appear in a message box. I am not 100% sure what you were doing wrong before because it seems to work fine for me. Let me know if this example helps at all...

Bene

Well, i do not see why this does not work
what i can suggest is this
you have declared the variable inside the teh sub, this makes them accessable ONLy inside that sub
so if you are calling that variable outside the sub later, it WILL NOT exist, example

public sub FindMid(byVal strWord as string)
dim strMidWord as string
strMidWord = mid(strWord,3,5)
end FindMid

call FindMid("hello my name is michal")
form1.print strMidWord

thsi wouyld give you an error message saying that the variable does not exist... like you said
if you declare strMidWord in the general declarations section, you will not get this problem, to test if this is the problem, put a msgbox that outputs the middle word inside the procedure

also, i see that you are declaring your variables as follows:
dim a, b, c as type
what this does is set c as the type specified, and leaves a and b as varients
the proper way would be,
dim a as type, b as type, c as type
unless of course you want varients....
test it out, try making dim a, b, c as integer, assign a string value to a, and itll crash is it is an integer variable....

If kissing a smoker is like kissing an ashtray, is eating an egg like tasting a chickens ***?

OK- First, thank you for the help guys. I tried all the suggestions and still can't get the function to work. But, I went to a friends computer and tried the same code, it ran fine. On my computer I still get the same "Compile Error" "Wrong number of arguments...." whether the code is in a module, form, or elsewhere. I did notice in the code that the function is not capitalized-

string = mid(anotherstring, 1, 3) instead of
string = Mid(anotherstring, 1, 3)

Guess this means the function isn't recognized????

Hope someone can help cause I'm smooth out of ideas.

Okay... you had mentioned that the Left and Right functions worked? I wrote you a new function called NewMid that should do everything that Mid does. Let me know what you think... it takes the same parameters as Mid normally would. I just tested it and it behaves identical, so hopefully this helps you out some.

Public Function NewMid(strInput As String, intStart As Integer, _
intLen As Integer) As String

' Define teh necessary variables.
Dim strTemp, strTemp2, strMid
Dim x As Long

' Loop through the input string looking at one character at a time.
For x = 1 To Len(strInput)
strTemp = Left(strInput, x)
strTemp2 = Right(strTemp, 1)

' Here I am adding this character to the string to return if _
it is past the starting point and if the return string is not _
already long enough.
If x >= intStart Then
If Len(strMid) < intLen Then
strMid = strMid + strTemp2
End If
End If
Next

NewMid = strMid
End Function


Let me know if this doesn't work!

Bene

Sounds like either your VBA dll is corrupt or missing OR you have created a public function Mid somewhere that is overriding the VBA Mid function.

Open the object browser (press [F2]) in the VB Editor. In the list of objects in the first combo you should have one saying VBA.

If you haven't then the problem is that you haven't got a reference to the VBA object (with all the functions, etc). To do this go to Tools-&gt;References in the VB editor window, and make sure Visual Basic for Applications is ticked and <font color=red>at the top of the list</font color=red> Use the priority arrows if not.

If you do have a reference then make sure the actual file exists (select line in list box and view filename that comes up below) and that it is valid.
On my machine it is pointing to <font color=blue>C:\PROGRAM FILES\COMMON FILES\MICROSOFT SHARED\VBA\VBA332.DLL</font color=blue>.


See how you go with that!

HTH

<font color=blue>Better to remain a fool than write a quote and remove all doubt. (****!!)</font color=blue>

Success!!! My sincere thanks to bene, kingesk, Little3lue, and karimahta. Turns out karimahta hit the nail on the head, I found a public funtion named "mid". I changed the name and now the other mid function works like a champ. Thanks.