Global variable for all functions in a macro

Zeus

Forums

Hi,
I need two variables that can be used in the two different functions within my macro, but instead of defining the variables within each function, I would like to define them at the begining and make them usable for all functions, but I cannot make it work. Somebody that has done this before? Thanks
 
Sub Main
'define variables  
Dim Qtr As String
Dim FY As String
Qtr = "Q4"
FY = "21"
 
Call TopNExtraction()'T&E Report - Q4 FY21.IMD
Call AppendField()'T&E Report List.IMD
End Sub
 
' Get list of T&E reports 
Function TopNExtraction
Set db = Client.OpenDatabase("T&E Report - " & Qtr & " " & FY &".IMD")
Set task = db.TopRecordsExtraction
task.AddFieldToInc "COMPANY_CODE"
task.AddFieldToInc "REPORT_ID"
task.AddFieldToInc "LAST_SUBMITTED_DATE"
task.AddFieldToInc "TRANSACTION_DATE"
task.AddKey "REPORT_ID", "A"
task.AddKey "TRANSACTION_DATE", "D"
dbName = "" & Qtr & " " & FY &"T&E Report List.IMD"
task.OutputFileName = dbName
task.NumberOfRecordsToExtract = 1
task.PerformTask
Set task = Nothing
Set db = Nothing
Client.OpenDatabase (dbName)
End Function
 
' Add Field
Function AppendField
Set db = Client.OpenDatabase("" & Qtr & " " & FY &"T&E Report List.IMD")
Set task = db.TableManagement
Set field = db.TableDef.NewField
field.Name = "DAYS_OF_DIFF"
field.Description = "Added field"
field.Type = WI_VIRT_NUM
field.Equation = "@Age( LAST_SUBMITTED_DATE ,  TRANSACTION_DATE )"
field.Decimals = 0
task.AppendField field
task.PerformTask
Set task = Nothing
Set db = Nothing
Set field = Nothing
End Function
 
 

Brian Element Thu, 11/04/2021 - 06:22

Hi Zeus, the global variables need to be before the sub main line.  So in your scenario take your two global variables:

Dim Qtr As String
Dim FY As String

And place them before the sub main like this:

Dim Qtr As String
Dim FY As String

 

Sub Main

End Sub

They will now be global and available to every functions.

Zeus Thu, 11/04/2021 - 13:17

Thank you for your reply Brian. However, after trying to run the script, I get the message "variable not defined"; I have tried defining them before the sub main line and within the sub main but I get the same message all the time: (on the first line of the Function TopNExtraction I get the message "variable not defined": 
 
Option Explicit
Dim Qtr As String
Dim FY As String
Qtr = "Q4"
FY = "FY21"
 
Sub Main
Call TopNExtraction()'T&E Report - Q4 FY21.IMD
Call AppendField()'T&E Repor List.IMD
 
End Sub
 
Function TopNExtraction
Set db = Client.OpenDatabase("T&E Report - " & Qtr & " " & FY &".IMD")
 

Zeus Thu, 11/04/2021 - 15:02

I made a few changes and now it works:
1) I moved the Qtr = "Q4" and FY = "FY21" below Sub Main
2) I removed the "Option Explicit" before declaring the variables at the top.
 
After doing this, it worked.
Thank you