Hello from Poland!

I am building a module with VBA, where sub's names are defined in
worksheet's cells. I need to determine if a string entered into cell can be
a proper VBA function name. I don't want to iterate and check each character
in a string. I think rather about any internal excel function or errors
trapping.
Any ideas?

How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


On Nov 11, 8:19 am, "MiM" wrote:
Hello from Poland!

I am building a module with VBA, where sub's names are defined in
worksheet's cells. I need to determine if a string entered into cell can be
a proper VBA function name. I don't want to iterate and check each character
in a string. I think rather about any internal excel function or errors
trapping.
Any ideas?

Sorry, code should be

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(myArray())
If cell = myArray(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub

(I always do that!)


On Nov 11, 8:39 am, JP wrote:
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub

This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.

HTH,
JP

Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents so
I must check if they follow visual basic naming rules. In this example only
first, second and last cell can be a valid function identifier. How can I
check this?

Val Like "#*" Or Val Like "* *"

InStr would be good too.

--
Tim Zych
SF, CA

"MiM" wrote in message ...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents
so I must check if they follow visual basic naming rules. In this example
only first, second and last cell can be a valid function identifier. How
can I check this?

I don't think Tim's suggestions will quite work because some chars - like
"/" or "?" are not allowed. Also the length is limited to 31 chars.

I think a good way is to just test it by attempting to add a dummy module
with the name and surrounding the attempt with "On Error Goto Next" and "On
Error Goto 0".

You probably know this, but for XL XP and later you'll also have to test
whether "Trust Access to the Visual Basic Project" is allowed under
ToolsMacroSecurityTrusted Publishers. So, test for that first.

hth,

Doug

"MiM" wrote in message ...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents
so I must check if they follow visual basic naming rules. In this example
only first, second and last cell can be a valid function identifier. How
can I check this?

I interpreted his question as "not wanting to iterate through each
character", not "give me all the possible allowed procedure naming rules."
His post is unclear. Maybe he needs both.

For the actual rules, he can look at the VBA help file "Visual Basic Naming
Rules", and I think using LIKE and InStr will help him.

He also has to avoid duplicate procedure names, etc.

Actually the allowable length of proc names is 255 chars.


--
Tim Zych
SF, CA

"Doug Glancy" wrote in message
...
I don't think Tim's suggestions will quite work because some chars - like
"/" or "?" are not allowed. Also the length is limited to 31 chars.

I think a good way is to just test it by attempting to add a dummy module
with the name and surrounding the attempt with "On Error Goto Next" and
"On Error Goto 0".

You probably know this, but for XL XP and later you'll also have to test
whether "Trust Access to the Visual Basic Project" is allowed under
ToolsMacroSecurityTrusted Publishers. So, test for that first.

hth,

Doug

"MiM" wrote in message
...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents
so I must check if they follow visual basic naming rules. In this
example only first, second and last cell can be a valid function
identifier. How can I check this?

Sorry, forget what I said about length - I was thinking of module names, not
subroutines.

Doug

"Doug Glancy" wrote in message
...
I don't think Tim's suggestions will quite work because some chars - like
"/" or "?" are not allowed. Also the length is limited to 31 chars.

I think a good way is to just test it by attempting to add a dummy module
with the name and surrounding the attempt with "On Error Goto Next" and
"On Error Goto 0".

You probably know this, but for XL XP and later you'll also have to test
whether "Trust Access to the Visual Basic Project" is allowed under
ToolsMacroSecurityTrusted Publishers. So, test for that first.

hth,

Doug

"MiM" wrote in message
...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents
so I must check if they follow visual basic naming rules. In this
example only first, second and last cell can be a valid function
identifier. How can I check this?

Tim,

What do you think of the idea of just trying to add the procedure and
checking for errors? With something like this where there are quite a few
possible pitfalls, it seems to me like just trying it with error trapping
works best.

Doug

"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
I interpreted his question as "not wanting to iterate through each
character", not "give me all the possible allowed procedure naming rules."
His post is unclear. Maybe he needs both.

For the actual rules, he can look at the VBA help file "Visual Basic
Naming Rules", and I think using LIKE and InStr will help him.

He also has to avoid duplicate procedure names, etc.

Actually the allowable length of proc names is 255 chars.


--
Tim Zych
SF, CA

"Doug Glancy" wrote in message
...
I don't think Tim's suggestions will quite work because some chars - like
"/" or "?" are not allowed. Also the length is limited to 31 chars.

I think a good way is to just test it by attempting to add a dummy module
with the name and surrounding the attempt with "On Error Goto Next" and
"On Error Goto 0".

You probably know this, but for XL XP and later you'll also have to test
whether "Trust Access to the Visual Basic Project" is allowed under
ToolsMacroSecurityTrusted Publishers. So, test for that first.

hth,

Doug

"MiM" wrote in message
...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells
contents so I must check if they follow visual basic naming rules. In
this example only first, second and last cell can be a valid function
identifier. How can I check this?

As others have suggested, you would just add some more criteria inside
the "For i = 0 to ubound(array())" loop. For example

If instr(1, cell, "*[1234567890]*") then
msgbox "error"
end if


Sorry I didn't test this code but hopefully you get the idea.


HTH,
JP


On Nov 11, 9:36 am, "MiM" wrote:
Uzytkownik "JP" napisal w wiadomoscinews:1194788390.259700.78250@19g2000hsx. googlegroups.com...





How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub

This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.

HTH,
JP

Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents so
I must check if they follow visual basic naming rules. In this example only
first, second and last cell can be a valid function identifier. How can I
check this?- Hide quoted text -

- Show quoted text -

Sorry (again) that should probably be

if Left(cell, 1) islike "*[1234567890]*" then
msgbox "error"
end if


--JP

On Nov 11, 1:21 pm, JP wrote:
As others have suggested, you would just add some more criteria inside
the "For i = 0 to ubound(array())" loop. For example

If instr(1, cell, "*[1234567890]*") then
msgbox "error"
end if

Sorry I didn't test this code but hopefully you get the idea.

HTH,
JP

On Nov 11, 9:36 am, "MiM" wrote:



Uzytkownik "JP" napisal w wiadomoscinews:1194788390.259700.78250@19g2000hsx. googlegroups.com...

How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub

This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.

HTH,
JP

Yes, but this is only one check if a cell contents is a reserved word. I
must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells contents so
I must check if they follow visual basic naming rules. In this example only
first, second and last cell can be a valid function identifier. How can I
check this?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

How would that work? If the procedure doesn't compile, code won't run.

The rules seem straightforward enough to write a validation function, and
also use code to compare previously-added macro names to ensure no dupes are
going to be added.

Tim


"Doug Glancy" wrote in message
...
Tim,

What do you think of the idea of just trying to add the procedure and
checking for errors? With something like this where there are quite a few
possible pitfalls, it seems to me like just trying it with error trapping
works best.

Doug

"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
I interpreted his question as "not wanting to iterate through each
character", not "give me all the possible allowed procedure naming rules."
His post is unclear. Maybe he needs both.

For the actual rules, he can look at the VBA help file "Visual Basic
Naming Rules", and I think using LIKE and InStr will help him.

He also has to avoid duplicate procedure names, etc.

Actually the allowable length of proc names is 255 chars.


--
Tim Zych
SF, CA

"Doug Glancy" wrote in message
...
I don't think Tim's suggestions will quite work because some chars - like
"/" or "?" are not allowed. Also the length is limited to 31 chars.

I think a good way is to just test it by attempting to add a dummy
module with the name and surrounding the attempt with "On Error Goto
Next" and "On Error Goto 0".

You probably know this, but for XL XP and later you'll also have to test
whether "Trust Access to the Visual Basic Project" is allowed under
ToolsMacroSecurityTrusted Publishers. So, test for that first.

hth,

Doug

"MiM" wrote in message
...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run. It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word.
I must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with some
macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells
contents so I must check if they follow visual basic naming rules. In
this example only first, second and last cell can be a valid function
identifier. How can I check this?

Here's some code that will catch nearly all invalid names. In VBA, set a
reference to "TypeLib Info". Then run the procedure CreateList to create the
list of VBA keywords, and then call IsValidProcName to see if a specific
string is a valid proc name.


Sub CreateList()

Dim FName As String
Dim TLIApp As TLI.TLIApplication
Dim TLITypeLibInfo As TLI.TypeLibInfo
Dim TLIMemInfo As TLI.MemberInfo
Dim TLITypeInfo As TLI.TypeInfo
Dim N As Long
Dim R As Long
Dim RR As Range

FName = ThisWorkbook.VBProject.References("VBA").FullPath
Set TLIApp = New TLI.TLIApplication
Set TLITypeLibInfo = TLIApp.TypeLibInfoFromFile(Filename:=FName)
With TLITypeLibInfo.TypeInfos
For N = 1 To .Count
On Error Resume Next
For Each TLIMemInfo In .Item(N).Members
R = R + 1
Worksheets("Sheet1").Cells(R, 1) = TLIMemInfo.Name
Next
On Error GoTo 0
Next N

End With
With Worksheets("Sheet1")
Set RR = .Range(.Cells(1, 1), .Cells(R, 1))
End With
ThisWorkbook.Names.Add Name:="KeyWords", RefersTo:=RR
End Sub


Function IsValidProcName(ProcName As String) As Boolean
Dim V As Variant
If ProcName Like "[A-Za-z]*" Then
If InStr(1, ProcName, Chr(32), vbBinaryCompare) = 0 Then
V = Application.Match(ProcName, Range("KeyWords"), 0)
If IsError(V) = False Then
IsValidProcName = False
Else
IsValidProcName = True
End If
Else
IsValidProcName = False
End If
Else
IsValidProcName = False
End If

End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"MiM" wrote in message ...
Hello from Poland!

I am building a module with VBA, where sub's names are defined in
worksheet's cells. I need to determine if a string entered into cell can
be a proper VBA function name. I don't want to iterate and check each
character in a string. I think rather about any internal excel function or
errors trapping.
Any ideas?

Tim,

Right you are. I was thinking it would error when the function was added,
but of course it won't until compile time as you say. Thanks for the
education.

Doug

"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
How would that work? If the procedure doesn't compile, code won't run.

The rules seem straightforward enough to write a validation function, and
also use code to compare previously-added macro names to ensure no dupes
are going to be added.

Tim


"Doug Glancy" wrote in message
...
Tim,

What do you think of the idea of just trying to add the procedure and
checking for errors? With something like this where there are quite a
few possible pitfalls, it seems to me like just trying it with error
trapping works best.

Doug

"Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message
...
I interpreted his question as "not wanting to iterate through each
character", not "give me all the possible allowed procedure naming
rules." His post is unclear. Maybe he needs both.

For the actual rules, he can look at the VBA help file "Visual Basic
Naming Rules", and I think using LIKE and InStr will help him.

He also has to avoid duplicate procedure names, etc.

Actually the allowable length of proc names is 255 chars.


--
Tim Zych
SF, CA

"Doug Glancy" wrote in message
...
I don't think Tim's suggestions will quite work because some chars -
like "/" or "?" are not allowed. Also the length is limited to 31
chars.

I think a good way is to just test it by attempting to add a dummy
module with the name and surrounding the attempt with "On Error Goto
Next" and "On Error Goto 0".

You probably know this, but for XL XP and later you'll also have to
test whether "Trust Access to the Visual Basic Project" is allowed
under ToolsMacroSecurityTrusted Publishers. So, test for that
first.

hth,

Doug

"MiM" wrote in message
...
Uzytkownik "JP" napisal w wiadomosci
ps.com...
How about something like this? Select your cells first, then run.
It's
a simple loop to check a list of cells against a list of reserved
words.

Sub CheckFunctionNameError()
Dim cell as Range

myArray = Array("cells","range","function","end","call","run ")

For each cell in selection
For i = 0 to ubound(array())
If cell = array(i) then
msgbox "One of your cells is using a reserved
word!",vbcritical
End if
Next i
Next cell

End Sub


This is air code, I did not test it. Just add the reserved words to
the Array(), I only added the ones I could think of off the top.


HTH,
JP


Yes, but this is only one check if a cell contents is a reserved word.
I must check if it follows VB naming rules.
Maybe my English is so poor, but this is not what I want.
Once again
I have some cells in worksheet, and I want to create a module with
some macros named like these cells contents.
So, for example I have in column data like this:
AAAA
bbbb
12a
aaa and bbb
cccc

Now I must to create 5 macros with the names determined by cells
contents so I must check if they follow visual basic naming rules. In
this example only first, second and last cell can be a valid function
identifier. How can I check this?

Thanks for all answers.
On the start this work I was trying to trap compile-time errors but it
seems unavailable. I see, I must to write validation function, something
like Chip's function IsValidProcName() but with my locale chartacters and it
must be more complicated becouse asterix * in Like statement matches
unallowed characters like $, &, ! etc...
Finnaly iteration :( ... or good instructions for users :)

Greetings from Poland
Micha³