Using VBA to call a function within a module.

[mnepic]

Ok so situation is that, i have a CSV export written based of numerous text boxes. Due to CSV's being broken by characters such as "" and it being imperative that the outputted data is correct I need to ensure that special characters are not allowed.

The is relatively simple to do by adding the following code:

public function special_char(keyAscii As Integer)
    
Select Case keyAscii
        Case 48 To 57 'integers
        Case 40 To 41, 45 To 45, 32 To 32, 65 To 90, 97 To 122, 240 To 240  'letters, brackets, space     and hyphen
        Case vbKeyDecimal
        Case vbKeyBack
    Case Else
        alert
        keyAscii = 0
        Exit Sub
    End Select
End function

However, because i need this code to run on all textboxes it would be idea to include this code in its own function within the module.
However, regardless of whether this code is within a public function, public sub, or private sub, i simply cannot call it using either:

call special_char

or

special_char

Any help would be much appreciated. No doubt it is to do with the (keyAscii As integer), i am just a bit lost!
Thanks in advance!

 

[MarkK]

Neither of the examples you posted supply the parameter, keyAscii, that the function requires. Try ...

SpecialChar 123

Also, since your function does not return a value, you can use a Sub

Public Sub SpecialChar(KeyAscii as Intger)
[COLOR="Green"]   'performs an operation that does not return a value[/COLOR]
End Sub

 

[mnepic]

And using

Special_char 123 would work?

Baring in mind that this functionality needs to ideally be built into the "on_key_Press" event.

So that every time the user presses a key it is checked.

 

[MarkK]

Couldn't you also remove illegal characters just before writing to the csv? It's not clear to me that the only solution is to interrogate every single keystroke.

Special_char 123 would work?

Try it. See what happens.

 

[mnepic]

I am sure there are numerous ways of accomplishing this to be honest. It is just that the code I presented works if tied directly to an event. It was more of a curiousity/learning thing for me to work out why it wouldn't work as part of a module.

I will give it a try in work in the morning. I shall feedback then!

Many thanks!

 

[mnepic]

For anyone reading this in future that wishes to replicate the code.

The trick is to pass the sub the keyascii value.

So.

special_chars keyascii

.

Should work nicely! Have a nice day!