# VBA - Copy value from variables after data processing



## Calvin1128 (Dec 17, 2022)

Hi all! 

I have worked on a simple data processing VBA, removing ":", ";"... from inputs, and going to input the value for further VBA code. 
It successfully returns to what I want. Like A-B:;*CHJK:8:89- A, It will return as ABC HJK889A (Though the code looks like stupid haha)
But could I copy the value of kw in the clipboard as well for my later operational use? It seems easy but I cannot find out a solution. 
Hope you may help! Thanks sincerely!

**
Sub Name()

Dim qw As String

qw = InputBox("Enter search value")

we = Application.WorksheetFunction.Substitute(qw, "-", "")
er = Application.WorksheetFunction.Substitute(we, ",", "")
rt = Application.WorksheetFunction.Substitute(er, " ", "")
ty = Application.WorksheetFunction.Substitute(rt, ":", "")
oi = Application.WorksheetFunction.Substitute(ty, ";", "")
op = Application.WorksheetFunction.Substitute(oi, "*", "")
yu = Application.WorksheetFunction.Substitute(op, " ", "")

kw = Trim(UCase(Left(yu, Len(yu) - 7) & " " & Right(yu, 7)))

...

End Sub
**


----------



## dmt32 (Dec 17, 2022)

Hi,
making your variable Public may do what you want.

See if update to your code helps you

Place code in a STANDARD module


```
*Public qw As String*

Sub GetName()
    Dim ReplaceChars As Variant, Entry As Variant
   
    ReplaceChars = Array("-", ",", ":", ";", "*", " ")
   
    Do
        Entry = InputBox("Enter search value", "Search")
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Sub
    Loop Until Len(Entry) > 9 '< min string entry lenght (change as required)
   
    'replace unwanted chars
    For x = LBound(ReplaceChars) To UBound(ReplaceChars)
        Entry = Replace(Entry, ReplaceChars(x), "", 1)
    Next x
   
    Entry = Trim(UCase(Left(Entry, Len(Entry) - 7) & " " & Right(Entry, 7)))
   
    qw = Entry
   
End Sub
```

Note the variable is placed at the very *TOP* of the code page *OUTSIDE *any procedure

Also note that I have renamed your code from Name to GetName. *Name* is a keyword used widely in the application & its use as a variable or procedure name is probably best avoided.

to call your code


```
Sub aTest()
    If Len(qw) = 0 Then GetName
   
    'your code
   
    MsgBox qw
End Sub
```

If the variable *qw* is empty then GetName code is called to intialize it

Hope Helpful

Dave


----------



## Calvin1128 (Dec 17, 2022)

Thanks! Dave, 

Thanks for the solution! More VBA knowledge learned from the code update and rename 
And it helps me easier to edit unwanted characters in the future!

But could "qw" value be copied after data processing? 
For example,
I enter "-B:;*CHJK:8:89- A," to the input box, and it returns as ABC HJK889A
Currently, I have to control + C myself and paste it somewhere else, like Outlook. 

Could it be copied already once the VBA is finished? I studied a little bit on Range.Copy but I cannot figure out a way to solve it. 
Hope you may help!


----------



## dmt32 (Dec 17, 2022)

Calvin1128 said:


> could "qw" value be copied after data processing?



Possibly - have family here at moment but look here:Paste, PutInClipboard, SetText methods example

and see if this will help you

Dave


----------



## Calvin1128 (Dec 17, 2022)

Thanks again! I am studying on PutInClipboard and it seems workable on my Macbook but not on my window desktop...
It returns "￿￿". I am not quite sure what I did wrong. Is there any alternative to solve it? Hope you may help when you are free 🙏 Thanks!


**
Public qw As String

Sub GetName()
    Dim ReplaceChars As Variant, Entry As Variant
    Dim CPY As New MSForms.DataObject

    ReplaceChars = Array("-", ",", ":", ";", "*", " ")

    Do
        Entry = InputBox("Enter search value", "Search")
        'cancel pressed
        If StrPtr(Entry) = 0 Then Exit Sub
    Loop Until Len(Entry) > 9 '< min string entry lenght (change as required)

    'replace unwanted chars
    For x = LBound(ReplaceChars) To UBound(ReplaceChars)
        Entry = Replace(Entry, ReplaceChars(x), "", 1)
    Next x

    Entry = Trim(UCase(Left(Entry, Len(Entry) - 7) & " " & Right(Entry, 7)))
    qw = Entry
    CPY.SetText qw
    CPY.PutInClipboard

End Sub
**


----------



## Calvin1128 (Dec 19, 2022)

Hi Dave! I have found a way to solve it. Thanks a lot!


----------



## dmt32 (Dec 19, 2022)

Calvin1128 said:


> Hi Dave! I have found a way to solve it. Thanks a lot!



sorry not got back to you done myself a mischief playing with the grandkids!
Glad you have resolved yourself - may though want to publish what you have done to help others searching same question

Dave


----------

