Hi,
I have a program (form) that allow the user to create differents tables and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName. What I
could do is to load the T(10) vector at the opening of the form and refer to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre

Guessing, but maybe

Dim T1 As Object

Set T1 = fSheetName(TableType(1))
temp = T1

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Alex St-Pierre" wrote in message
...
Hi,
I have a program (form) that allow the user to create differents tables
and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName.
What I
could do is to load the T(10) vector at the opening of the form and refer
to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say
T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre

You can use a user-defined type for this:

Option Explicit

Type TableType
Type As String
TypeName As String
SheetName As String
' etc etc
End Type

Sub testTableType
dim T(10) as TableType
T(1).Type = "x"
T(1).TypeName = "xxx"
' etc etc
End Sub



Alex St-Pierre wrote:
Hi,
I have a program (form) that allow the user to create differents tables and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName. What I
could do is to load the T(10) vector at the opening of the form and refer to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre

Hi,
Can I write in the module1:
Public Type TableType 'I don't know the difference between Type and Stucture??
Type As String
TypeName As String
SheetName As String
' etc etc
End Type

Function InitilializeT()
dim T(10) as TableType
T(1).Type = "x"
T(1).TypeName = "xxx"
' etc
Function Sub

In the form:
Private Sub UserForm_Initialize()
temp = InitializeT() ' T will stay in memory until the end of the macro.
'etcEnd Sub

To make a search inside T(), I will use:
For i = 1 to T.Items.Count
If T(i).Type = "T1-1" Then mySheet = T(i).SheetName
Next i

--
Alex St-Pierre


"Andrew Taylor" wrote:

You can use a user-defined type for this:

Option Explicit

Type TableType
Type As String
TypeName As String
SheetName As String
' etc etc
End Type

Sub testTableType
dim T(10) as TableType
T(1).Type = "x"
T(1).TypeName = "xxx"
' etc etc
End Sub



Alex St-Pierre wrote:
Hi,
I have a program (form) that allow the user to create differents tables and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName. What I
could do is to load the T(10) vector at the opening of the form and refer to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre

I posted the macro below.
Is there a way to see how times it takes to load? I refer to other functions
because I also have text in french. Does Exit Function is the best way to
make program faster?
Thanks!
Alex

Option Explicit

Public Type TableType
Type As String 'Short Name
TypeName As String 'Long Name
SheetName As String
Title1 As String
Title2 As String
Link As Boolean
Count As Integer 'For i = 1 to T(1).Count will indicate 1 To 9.
End Type

Public iLang As Integer

Sub initializeT()
Dim T(9) As TableType
Dim aType As Variant
Dim iType As Integer
Dim iTable As Integer
iLang = 1 'English Text
aType = Array("", "T1-1", "T1-2", "T2-1", "T2-2", "T3-1", "T3-2", "T3-3",
"T3-4", "T3-5")
For iTable = 1 To 9
T(iTable).Count = 9
T(iTable).Type = aType(iTable)
T(iTable).TypeName = Application.VLookup(aType(iTable),
Sheets("data").Range("manager"), 2, 0)
T(iTable).Title1 = fTableTitle1(iTable)
T(iTable).Title2 = fTableTitle2(iTable)
Next iTable
iLang = 1
End Sub

Function fTableTitle1(i As Integer) As String
If i = 1 Then fTableTitle1 = fText("Table 1-1", "Tableau 1.1"): Exit
Function
If i = 2 Then fTableTitle1 = fText("Table 1-2", "Tableau 1.2"): Exit
Function
If i = 3 Then fTableTitle1 = fText("Table 2-1", "Tableau 2.1"): Exit
Function
If i = 4 Then fTableTitle1 = fText("Table 2-2", "Tableau 2.2"): Exit
Function
If i = 5 Then fTableTitle1 = fText("Table 3-1", "Tableau 3.1"): Exit
Function
If i = 6 Then fTableTitle1 = fText("Table 3-2", "Tableau 3.2"): Exit
Function
If i = 7 Then fTableTitle1 = fText("Table 3-3", "Tableau 3.3"): Exit
Function
If i = 7 Then fTableTitle1 = fText("Table 3-4", "Tableau 3.4"): Exit
Function
If i = 9 Then fTableTitle1 = fText("Table 3-5", "Tableau 3.5"): Exit
Function
End Function

Function fTableTitle2(i As Integer) As String
If i = 1 Then fTableTitle2 = fText("Going-Concern Financial Position",
"Résultats sur base de provisionnement"): Exit Function
If i = 2 Then fTableTitle2 = fText("Reconciliation of Going-Concern
Financial Position", "Rapprochement de la situation financière selon
lapproche de continuité"): Exit Function
If i = 3 Then fTableTitle2 = fText("Solvency Financial Position",
"Résultats sur base de solvabilité"): Exit Function
If i = 4 Then fTableTitle2 = fText("Table 2-2 Part (a) Adjustment",
"Ajustement de lactif de solvabilité"): Exit Function
If i = 5 Then fTableTitle2 = fText("Normal Actuarial Cost", "Coût
normal"): Exit Function
If i = 6 Then fTableTitle2 = fText("Reconciliation of Normal Actuarial
Cost", "Rapprochement du coût normal"): Exit Function
If i = 7 Then fTableTitle2 = fText("Amortization Payments €“ Previous
Valuation", "Cotisations déquilibre selon les évaluations précédentes"):
Exit Function
If i = 8 Then fTableTitle2 = fText("Amortization Payments €“ Current
Valuation", "Cotisations déquilibre selon l'évaluation courante"): Exit
Function
If i = 9 Then fTableTitle2 = fText("Required Contributions",
"Cotisations annuelles requises"): Exit Function
End Function

Function fText(English_Text As String, French_Text As String) As String
If iLang = 1 Then fText = English_Text
If iLang < 1 Then fText = French_Text
End Function

--
Alex St-Pierre


"Andrew Taylor" wrote:

You can use a user-defined type for this:

Option Explicit

Type TableType
Type As String
TypeName As String
SheetName As String
' etc etc
End Type

Sub testTableType
dim T(10) as TableType
T(1).Type = "x"
T(1).TypeName = "xxx"
' etc etc
End Sub



Alex St-Pierre wrote:
Hi,
I have a program (form) that allow the user to create differents tables and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName. What I
could do is to load the T(10) vector at the opening of the form and refer to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre

I don't know why T(1).Count is not defined in the function "fSheetName". T is
declared public but is not. Any idea?

Option Explicit

Public Type TableType
Count As Integer 'For i = 1 to T(1).Count will indicate 1 To 9.
Type As String 'Short Name
TypeName As String 'Long Name
SheetName As String
Title1 As String
Title2 As String
Link As Boolean
End Type

Public iLang As Integer
Public T

Sub initializeT()
Dim T(9) As TableType
Dim aType As Variant
Dim iType As Integer
Dim iTable As Integer
iLang = 1 'English Text
aType = Array("", "T1-1", "T1-2", "T2-1", "T2-2", "T3-1", "T3-2", "T3-3",
"T3-4", "T3-5")
For iTable = 1 To 9
T(iTable).Count = 9
T(iTable).Type = aType(iTable)
T(iTable).TypeName = Application.VLookup(aType(iTable),
Sheets("data").Range("manager"), 2, 0)
T(iTable).Link = True 'Voir avec VLOOKUP précédent
T(iTable).Title1 = fTableTitle1(iTable)
T(iTable).Title2 = fTableTitle2(iTable)
If T(iTable).Link Then T(iTable).SheetName =
fSheetName(T(iTable).TypeName)
Next iTable
iLang = 1
End Sub

Function fSheetName(sType1 As String) As String
Dim i As Integer
On Error Resume Next
i = T(1).Count
For i = 1 To T(1).Count
If Sheets(i).Evaluate("Type") = sType1 Then fSheetName =
Sheets(i).Name: Exit For
Next i
On Error GoTo 0
End Function
--
Alex St-Pierre


"Alex St-Pierre" wrote:

Hi,
I have a program (form) that allow the user to create differents tables and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName. What I
could do is to load the T(10) vector at the opening of the form and refer to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre

I found solution to the other questions. Thanks a lot...!! Using T(i).Type is
the best solution. It's a very powerfull tools! :))
--
Alex St-Pierre


"Alex St-Pierre" wrote:

I don't know why T(1).Count is not defined in the function "fSheetName". T is
declared public but is not. Any idea?

Option Explicit

Public Type TableType
Count As Integer 'For i = 1 to T(1).Count will indicate 1 To 9.
Type As String 'Short Name
TypeName As String 'Long Name
SheetName As String
Title1 As String
Title2 As String
Link As Boolean
End Type

Public iLang As Integer
Public T

Sub initializeT()
Dim T(9) As TableType
Dim aType As Variant
Dim iType As Integer
Dim iTable As Integer
iLang = 1 'English Text
aType = Array("", "T1-1", "T1-2", "T2-1", "T2-2", "T3-1", "T3-2", "T3-3",
"T3-4", "T3-5")
For iTable = 1 To 9
T(iTable).Count = 9
T(iTable).Type = aType(iTable)
T(iTable).TypeName = Application.VLookup(aType(iTable),
Sheets("data").Range("manager"), 2, 0)
T(iTable).Link = True 'Voir avec VLOOKUP précédent
T(iTable).Title1 = fTableTitle1(iTable)
T(iTable).Title2 = fTableTitle2(iTable)
If T(iTable).Link Then T(iTable).SheetName =
fSheetName(T(iTable).TypeName)
Next iTable
iLang = 1
End Sub

Function fSheetName(sType1 As String) As String
Dim i As Integer
On Error Resume Next
i = T(1).Count
For i = 1 To T(1).Count
If Sheets(i).Evaluate("Type") = sType1 Then fSheetName =
Sheets(i).Name: Exit For
Next i
On Error GoTo 0
End Function
--
Alex St-Pierre


"Alex St-Pierre" wrote:

Hi,
I have a program (form) that allow the user to create differents tables and
I have too much function inside VBA that have different input and output.
As example, for TableNumber = 1, there's a Type, TypeName, SheetName,
Title1,Title2,... corresponding with them.

Instead of using all these function (example: temp =
fSheetName(TableType(1))), I could used something like T(1).SheetName. What I
could do is to load the T(10) vector at the opening of the form and refer to
it thereafter.

Structure seems to be the right thing to simplify my program. Since my
program will be used by a lot of people, I'm wondering if it is a good
approach and if it can be used on any VB version? (I used 6.3).

I could use a table (because all datas inside T() are string) but I prefer
to write T(10).Title1 instead of T(10,3). Is there a way to say T(10).Title1
refer to element 3 inside a table T(10,3)?

Thanks a lot!!
--
Alex St-Pierre