MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VBA: using dimesioned strings in call procedures


Posted by SPalding on December 04, 2001 4:06 PM

I am trying to call a procedure which needs to understand what text strings have been dimmed. Is there a way to have a sub procedure that is callable from within another, or is there a way to dim the strings once the procedure has been called... or should I come up with some array function? I ultimately have to run the call procedure for 22 sets of text string data.

Thank you,
SPalding


Sub RunProgram()
Dim strA, strB, strC, strD, strE, strF, strG As String
Application.ScreenUpdating = False
'Sheet names below
strA = "T_P"
strB = "Print_TP"
strC = "T_B"
strD = "Print_TB"

'product codes below
strE = "PLAZ DISCOVERY PREFERRED"
strF = "2VHNZZ: DISCOVERY PREFERRED NONQUAL + 2VHZZZ: DISCOVERY PREFERRED"
strG = "2VHQZZ: DISCOVERY PREFERRED QUAL"
'Load Transfer Proof with codes
Call calculate
'new product code below
strE = "PLNJ DISCOVERY NOW"
strF = "4VHGZZ: DISCOVERY NOW NONQUAL + 4KOLD: DISCOVERY NOW"
strG = "4LLQZZ: DISCOVERY NOW QUAL"

call calculate

'enter new info below
End Sub


Sub calculate()
Sheets(strA).Select
Range("B8").Formula = strD
Range("C10").Formula = strE
Range("D10").Formula = strF
'Run Transfer Proof
Application.Run Macro:="esscode.xls!RetrieveFromEssbase"
'Print Transfer Proof
Sheets(strB).Select
Range("A6").Formula = strC
ActiveWindow.SelectedSheets.PrintOut Copies:=1
'run trial balance
Sheets(strC).Select
Range("b8").Formula = strC
Application.Run Macro:="esscode.xls!RetrieveFromEssbase"
'print trial balance
Sheets(strD).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub


Posted by Bariloche on December 04, 2001 8:26 PM

SPalding,

Looks like the difficulty you are experiencing stems from how and where you are declaring your variables. As you have it written, your variables are only available to the subroutine "RunProgram". In order for them to be available module-wide you need to declare them outside of, and before, your first subroutine. Also, VBA does not recognize variable declarations separated by commas. As written, only "strG" is dimensioned as a string, the rest are "variant". In order for them to all be dimensioned as strings you need to explicitly indicate them like so:

Dim strA as String
Dim strB as String
etc

Or you can use this style:

Dim StrA as String, strB as String
etc

You might want to read through the Help topics on variables and the various levels of scope that they can have.


good luck

'product codes below call calculate 'enter new info below

Sub calculate() Sheets(strA).Select Range("B8").Formula = strD Range("C10").Formula = strE Range("D10").Formula = strF 'Run Transfer Proof Application.Run Macro:="esscode.xls!RetrieveFromEssbase" 'Print Transfer Proof Sheets(strB).Select Range("A6").Formula = strC ActiveWindow.SelectedSheets.PrintOut Copies:=1 'run trial balance Sheets(strC).Select Range("b8").Formula = strC Application.Run Macro:="esscode.xls!RetrieveFromEssbase" 'print trial balance Sheets(strD).Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub

Posted by Ivan F Moala on December 04, 2001 9:00 PM

SPalding,

as well as Bariloche good advice since your varables for sheet names are constant then you can also declare these as constants.....also
you could declare an array to store the other variables in 22 ?. Advantage of this is you can access them Globally. eg

Public Const strA As String = "Test"
Public Dim strArray() as string

then read in some strings into the array


Ivan

Looks like the difficulty you are experiencing stems from how and where you are declaring your variables. As you have it written, your variables are only available to the subroutine "RunProgram". In order for them to be available module-wide you need to declare them outside of, and before, your first subroutine. Also, VBA does not recognize variable declarations separated by commas. As written, only "strG" is dimensioned as a string, the rest are "variant". In order for them to all be dimensioned as strings you need to explicitly indicate them like so: Dim strA as String Dim strB as String etc Or you can use this style: Dim StrA as String, strB as String etc You might want to read through the Help topics on variables and the various levels of scope that they can have. good luck

: I am trying to call a procedure which needs to understand what text strings have been dimmed. Is there a way to have a sub procedure that is callable from within another, or is there a way to dim the strings once the procedure has been called... or should I come up with some array function? I ultimately have to run the call procedure for 22 sets of text string data. : Thank you, : SPalding

Posted by SPalding on December 05, 2001 1:03 PM

That is all too simple. Thank you gentlemen. SPalding, as well as Bariloche good advice since your varables for sheet names are constant then you can also declare these as constants.....also you could declare an array to store the other variables in 22 ?. Advantage of this is you can access them Globally. eg Public Const strA As String = "Test" Public Dim strArray() as string then read in some strings into the array Ivan : Looks like the difficulty you are experiencing stems from how and where you are declaring your variables. As you have it written, your variables are only available to the subroutine "RunProgram". In order for them to be available module-wide you need to declare them outside of, and before, your first subroutine. Also, VBA does not recognize variable declarations separated by commas. As written, only "strG" is dimensioned as a string, the rest are "variant". In order for them to all be dimensioned as strings you need to explicitly indicate them like so

: