VBA: using dimesioned strings in call procedures

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

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

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

: