Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Mid() function


Reply
 
Thread Tools Display Modes
  #1  
Old 05-23-2001, 11:37 AM
mdobs
Guest
 
Posts: n/a
Default 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.

Reply With Quote
  #2  
Old 05-23-2001, 04:09 PM
bene bene is offline
Centurion
 
Join Date: May 2001
Location: St. Paul, MN
Posts: 145
Default Re: Mid() function

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...

__________________
Bene ;)
Reply With Quote
  #3  
Old 05-23-2001, 04:17 PM
kingesk
Guest
 
Posts: n/a
Default Re: Mid() function

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

Reply With Quote
  #4  
Old 05-23-2001, 06:19 PM
mdobs
Guest
 
Posts: n/a
Default Re: Mid() function

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.


Reply With Quote
  #5  
Old 05-23-2001, 06:30 PM
bene bene is offline
Centurion
 
Join Date: May 2001
Location: St. Paul, MN
Posts: 145
Default Re: Mid() function

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


__________________
Bene ;)
Reply With Quote
  #6  
Old 05-23-2001, 07:52 PM
LittLe3Lue
Guest
 
Posts: n/a
Question Re: Mid() function

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 ***?
Reply With Quote
  #7  
Old 05-24-2001, 09:35 AM
mdobs
Guest
 
Posts: n/a
Default Re: Mid() function

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.

Reply With Quote
  #8  
Old 05-24-2001, 11:28 AM
bene bene is offline
Centurion
 
Join Date: May 2001
Location: St. Paul, MN
Posts: 145
Wink Re: Mid() function

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

__________________
Bene ;)
Reply With Quote
  #9  
Old 05-24-2001, 08:16 PM
karimahta karimahta is offline
Senior Contributor

* Guru *
 
Join Date: Mar 2000
Location: Christchurch, New Zealand
Posts: 470
Default Re: Mid() function

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>
Reply With Quote
  #10  
Old 05-25-2001, 04:51 PM
mdobs
Guest
 
Posts: n/a
Default Re: Mid() function

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.

Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->