Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 12th, 2005, 12:36 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Passing by reference

I am having difficulty getting this subfunction of mine to work because when I pass it something and then change it it goes back to its original value in the main function.

Example:

    symrem (curword) 'removes symbols from the word currently being looked at

if I pass "don't" to symrem as the string and remove the apostrophe in the symrem procedure the symbol is still there when I go back to the main procedure. curword is still "don't". I am sure that the apostrophe is removed in the symrem procedure as I have watched it debug several times.

What am I missing that is causing the word not to change?
Is there any simple function that I can use to remove all the symbols from something?
Is it possible to make a function that returns something similar to in C++?

Reply With Quote
  #2 (permalink)  
Old November 12th, 2005, 12:56 AM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

In case it's relevant this is my sub procedure:

Public Sub symrem(nw)

Do
nwold = nw

nw = Replace(nw, ".", "") 'takes out periods
nw = Replace(nw, ",", "") 'takes out commas
nw = Replace(nw, ",", "")

For i = 33 To 64 'takes out all non-letter characters
nw = Replace(nw, Chr$(i), "")
Next i

For i = 91 To 96
nw = Replace(nw, Chr$(i), "") 'takes out all non-letter characters
Next i

For i = 123 To 155
nw = Replace(nw, Chr$(i), "") 'takes out all non-letter characters
Next i

If nwold = nw Then
    Exit Do
End If
Loop


End Sub

Reply With Quote
  #3 (permalink)  
Old November 12th, 2005, 05:54 AM
Registered User
 
Join Date: Oct 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Not sure if this is the problem as I havent seen the calling and called procedure. Do you have the brackets on the calling procedure IE

Sub CallingProcedure()
Dim curword as string
'Some code here
'then call the procedure
symrem curword
End sub

'Called procedure
symrem (curword)
'Do some stuff to curword
End sub

Hope this helps
Regards
Rob
Reply With Quote
  #4 (permalink)  
Old November 14th, 2005, 12:40 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

not sure what your trying to achieve here, if its simply to strip out the characters then it would be best in a function, something liek this

Public Function symrem(nw) As String
For iLoop = 1 To Len(nw)
    Select Case Asc(Mid(nw, iLoop, 1))
        Case 65 To 90, 97 To 122
            NewString = NewString & Mid(nw, iLoop, 1)
    End Select
Next iLoop
symrem = NewString
End Function

Is that what you mean ?

Reply With Quote
  #5 (permalink)  
Old November 19th, 2005, 06:07 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

So if you called the function as:
newword=symrem(nw)

then:

symrem=newstring is how you would return the value of newstring?

My problem was I didn't know how to return a value from a function in basic, I thought you had to do it all by reference.

Reply With Quote
  #6 (permalink)  
Old November 21st, 2005, 03:49 AM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

well if sData was the variable containing the word you wanted stripping (for example "don't" then you woould have something like

sNewWord = symrem(sData)

That would pass the value of the variable (sData) to the function (symrem), which would then strip out the characters not needed and return the value to the specified variable (sNewWord)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
passing a const reference in C#.NET texasraven C# 2 December 21st, 2004 06:48 PM
Passing clas by reference ThunderBird Visual C++ 5 August 6th, 2004 06:26 AM
Passing a class by reference James Diamond VB How-To 13 February 16th, 2004 06:07 AM
Passing Reference Types by Value semiloof VS.NET 2002/2003 1 December 22nd, 2003 12:16 AM
Passing by reference jacob ASP.NET 1.0 and 1.1 Basics 1 July 12th, 2003 05:07 PM



All times are GMT -4. The time now is 06:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.