Paradox with constants
 

I have a set of procedures in which I use the following code:

Counter = 1
Do
If Counter <= 5 Then
Cells(9, 3 + (Counter * 2)).Font.Name = fnt(Counter)
Cells(9, 3 + (Counter * 2)).Font.Size = (Counter * 2) + 30
Cells(9, 3 + (Counter * 2)).Font.ColorIndex = (Counter * 2) + 3
Cells(9, 3 + (Counter * 2)) = Mid(MyString, Counter, 1)
End If
If Counter = 6 Then
Cells(11, (Counter * 2) - 6).Font.Name = fnt(Counter)
Cells(11, (Counter * 2) - 6).Font.Size = (Counter * 2) + 30
Cells(11, (Counter * 2) - 6).Font.ColorIndex = (Counter * 2) + 5
Cells(11, (Counter * 2) - 6) = Mid(MyString, Counter, 1)
End If
Counter = Counter + 1
PlayExclam
WaitTime
Loop Until Counter = 10

This code works fine as long as I declare the variable fnt(1 thru n) within
the procedure. Example fnt(1) = "Arial" . When I try to make this a public
constant so it will be recognized in more than one procedure, the compiler
balks at the parentheses and if I remove the parentheses the counter will nor
concatenate.

What am I missing here?

Paradox with constants
 

Something like this...
'---------------------------
Option Explicit
Public fnt() As String


Sub ABC()
Dim counter As Long
ReDim fnt(1 To 10)

'assumes font names are in C1:C10
For counter = 1 To 10
fnt(counter) = Cells(counter, 3).Value
Next
counter = 1
----------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"JLGWhiz"

wrote in message
I have a set of procedures in which I use the following code:

Counter = 1
Do
If Counter <= 5 Then
Cells(9, 3 + (Counter * 2)).Font.Name = fnt(Counter)
Cells(9, 3 + (Counter * 2)).Font.Size = (Counter * 2) + 30
Cells(9, 3 + (Counter * 2)).Font.ColorIndex = (Counter * 2) + 3
Cells(9, 3 + (Counter * 2)) = Mid(MyString, Counter, 1)
End If
If Counter = 6 Then
Cells(11, (Counter * 2) - 6).Font.Name = fnt(Counter)
Cells(11, (Counter * 2) - 6).Font.Size = (Counter * 2) + 30
Cells(11, (Counter * 2) - 6).Font.ColorIndex = (Counter * 2) + 5
Cells(11, (Counter * 2) - 6) = Mid(MyString, Counter, 1)
End If
Counter = Counter + 1
PlayExclam
WaitTime
Loop Until Counter = 10

This code works fine as long as I declare the variable fnt(1 thru n) within
the procedure. Example fnt(1) = "Arial" . When I try to make this a public
constant so it will be recognized in more than one procedure, the compiler
balks at the parentheses and if I remove the parentheses the counter will nor
concatenate.
What am I missing here?

Paradox with constants
 

Thanks Jim, I guess I did not explain the problem very well. I had the basic
variable declared and was working but I had to specifically list fourteen
fonts i.e.
fnt(1) = "Arial", fnt(2) = "KristenITC", etc. in each procedure. It is the
list of fourteen specific fonts that I am trying to make public so I don't
have to list them in each procedure. It would be a lot simpler if Microsoft
assigned a constant to each font. Anyhow, is there a simple way to make the
list of fourteen variables public as constants and still be able to use the
counter to call them up?

"Jim Cone" wrote:

Something like this...
'---------------------------
Option Explicit
Public fnt() As String


Sub ABC()
Dim counter As Long
ReDim fnt(1 To 10)

'assumes font names are in C1:C10
For counter = 1 To 10
fnt(counter) = Cells(counter, 3).Value
Next
counter = 1
----------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"JLGWhiz"

wrote in message
I have a set of procedures in which I use the following code:

Counter = 1
Do
If Counter <= 5 Then
Cells(9, 3 + (Counter * 2)).Font.Name = fnt(Counter)
Cells(9, 3 + (Counter * 2)).Font.Size = (Counter * 2) + 30
Cells(9, 3 + (Counter * 2)).Font.ColorIndex = (Counter * 2) + 3
Cells(9, 3 + (Counter * 2)) = Mid(MyString, Counter, 1)
End If
If Counter = 6 Then
Cells(11, (Counter * 2) - 6).Font.Name = fnt(Counter)
Cells(11, (Counter * 2) - 6).Font.Size = (Counter * 2) + 30
Cells(11, (Counter * 2) - 6).Font.ColorIndex = (Counter * 2) + 5
Cells(11, (Counter * 2) - 6) = Mid(MyString, Counter, 1)
End If
Counter = Counter + 1
PlayExclam
WaitTime
Loop Until Counter = 10

This code works fine as long as I declare the variable fnt(1 thru n) within
the procedure. Example fnt(1) = "Arial" . When I try to make this a public
constant so it will be recognized in more than one procedure, the compiler
balks at the parentheses and if I remove the parentheses the counter will nor
concatenate.
What am I missing here?

Paradox with constants
 

Run the code I posted or this variation...

Option Explicit
Dim fnt As Variant

Sub ABC()
fnt = Array("Arial", "Arial Black", "Arial Narrow", "Arioso", "Bookman Old Style")
End Sub

'then run another sub...

Sub ProveItWorks()
MsgBox fnt(4)
End Sub
-------------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"JLGWhiz"

wrote in message
Thanks Jim, I guess I did not explain the problem very well. I had the basic
variable declared and was working but I had to specifically list fourteen
fonts i.e.
fnt(1) = "Arial", fnt(2) = "KristenITC", etc. in each procedure. It is the
list of fourteen specific fonts that I am trying to make public so I don't
have to list them in each procedure. It would be a lot simpler if Microsoft
assigned a constant to each font. Anyhow, is there a simple way to make the
list of fourteen variables public as constants and still be able to use the
counter to call them up?

"Jim Cone" wrote:

Something like this...
'---------------------------
Option Explicit
Public fnt() As String


Sub ABC()
Dim counter As Long
ReDim fnt(1 To 10)

'assumes font names are in C1:C10
For counter = 1 To 10
fnt(counter) = Cells(counter, 3).Value
Next
counter = 1
----------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"JLGWhiz"

wrote in message
I have a set of procedures in which I use the following code:

Counter = 1
Do
If Counter <= 5 Then
Cells(9, 3 + (Counter * 2)).Font.Name = fnt(Counter)
Cells(9, 3 + (Counter * 2)).Font.Size = (Counter * 2) + 30
Cells(9, 3 + (Counter * 2)).Font.ColorIndex = (Counter * 2) + 3
Cells(9, 3 + (Counter * 2)) = Mid(MyString, Counter, 1)
End If
If Counter = 6 Then
Cells(11, (Counter * 2) - 6).Font.Name = fnt(Counter)
Cells(11, (Counter * 2) - 6).Font.Size = (Counter * 2) + 30
Cells(11, (Counter * 2) - 6).Font.ColorIndex = (Counter * 2) + 5
Cells(11, (Counter * 2) - 6) = Mid(MyString, Counter, 1)
End If
Counter = Counter + 1
PlayExclam
WaitTime
Loop Until Counter = 10

This code works fine as long as I declare the variable fnt(1 thru n) within
the procedure. Example fnt(1) = "Arial" . When I try to make this a public
constant so it will be recognized in more than one procedure, the compiler
balks at the parentheses and if I remove the parentheses the counter will nor
concatenate.
What am I missing here?

Paradox with constants
 

OK Jim, I can work with what you gave me. It was not what I had in mind but
it is better that what I had space wise. Thanks for the assist.

"Jim Cone" wrote:

Run the code I posted or this variation...

Option Explicit
Dim fnt As Variant

Sub ABC()
fnt = Array("Arial", "Arial Black", "Arial Narrow", "Arioso", "Bookman Old Style")
End Sub

'then run another sub...

Sub ProveItWorks()
MsgBox fnt(4)
End Sub
-------------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"JLGWhiz"

wrote in message
Thanks Jim, I guess I did not explain the problem very well. I had the basic
variable declared and was working but I had to specifically list fourteen
fonts i.e.
fnt(1) = "Arial", fnt(2) = "KristenITC", etc. in each procedure. It is the
list of fourteen specific fonts that I am trying to make public so I don't
have to list them in each procedure. It would be a lot simpler if Microsoft
assigned a constant to each font. Anyhow, is there a simple way to make the
list of fourteen variables public as constants and still be able to use the
counter to call them up?

"Jim Cone" wrote:

Something like this...
'---------------------------
Option Explicit
Public fnt() As String


Sub ABC()
Dim counter As Long
ReDim fnt(1 To 10)

'assumes font names are in C1:C10
For counter = 1 To 10
fnt(counter) = Cells(counter, 3).Value
Next
counter = 1
----------------
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"JLGWhiz"

wrote in message
I have a set of procedures in which I use the following code:

Counter = 1
Do
If Counter <= 5 Then
Cells(9, 3 + (Counter * 2)).Font.Name = fnt(Counter)
Cells(9, 3 + (Counter * 2)).Font.Size = (Counter * 2) + 30
Cells(9, 3 + (Counter * 2)).Font.ColorIndex = (Counter * 2) + 3
Cells(9, 3 + (Counter * 2)) = Mid(MyString, Counter, 1)
End If
If Counter = 6 Then
Cells(11, (Counter * 2) - 6).Font.Name = fnt(Counter)
Cells(11, (Counter * 2) - 6).Font.Size = (Counter * 2) + 30
Cells(11, (Counter * 2) - 6).Font.ColorIndex = (Counter * 2) + 5
Cells(11, (Counter * 2) - 6) = Mid(MyString, Counter, 1)
End If
Counter = Counter + 1
PlayExclam
WaitTime
Loop Until Counter = 10

This code works fine as long as I declare the variable fnt(1 thru n) within
the procedure. Example fnt(1) = "Arial" . When I try to make this a public
constant so it will be recognized in more than one procedure, the compiler
balks at the parentheses and if I remove the parentheses the counter will nor
concatenate.
What am I missing here?