Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still filename problem
I am sorry to repost this but it looks like too much time is passed and the
solution never came. I have two statements that involve a string called "strMGR_SHORT_NAME that are having problems. In the first case, the statement is fname = StrMGR_SHORT_NAME & Sheets("INPUTS").Range("B45").Value & Sheets("INPUTS").Range("E11").Value & ".xls" The compiler error message I am getting says "object variable or with block variable not set." I am pretty sure this statement had been working but somehow now is not. I have had it show me that variable, which is entered thru an Input Box and it is what it's supposed to be. But the command is not working. Does this error message tell anyone anything? Then, this subroutine calls another subroutine where I use a similar statement to try to open a file. In this case, the statement that is crashing is: myFilename = StrMGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".L00" It was not an XLS file and that caused some confusion but it now seems evident that the real problem is that this same strMGR_SHORT_NAME is now blank and so is not automatically being "seen' by the called subroutine. So, the solution for this seems to be merely to pass the result of the INPUTBox along and so the question is, shouldn't a calling subroutine pass its definitions along to a subroutine it calls? And, if not, how do I get it to do so? Thanks, Grace |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still filename problem
The answer to your second question is easy, and you should look up
"Understanding Scope and Visibility" in XL/VBA Help. Variables declared within a module are only visible within the module: Public Sub foo() Dim vTest As Variant vTest = "test" bar End Sub Public Sub bar() MsgBox vTest End Sub In this case, bar() will display an empty message box. Incidentally, this is a superb reason to put "Option Explicit" at the top of your modules (choose Preference/Editor in the VBE, and check the Require variable declaration checkbox to make this automatic). If you'd had that, you would have gotten a compile error - variable not declared in bar(). To pass variables on, you could declare them globally (at the top of the module), which is a widely used technique, but one that sometimes gets beginning coders in trouble when two procedures operate on the same global. Dim vTest As Variant Public Sub foo() vTest = "test" bar End Sub Public Sub bar() MsgBox vTest End Sub Or you could explicitly pass them as an argument: Public Sub foo() Dim vTest As Variant vTest = "test" bar vTest End Sub Public Sub bar(ByRef vTest2 As Variant) MsgBox vTest End Sub this time, bar() will display "test" in the message box. In article , "Grace" wrote: I am sorry to repost this but it looks like too much time is passed and the solution never came. I have two statements that involve a string called "strMGR_SHORT_NAME that are having problems. In the first case, the statement is fname = StrMGR_SHORT_NAME & Sheets("INPUTS").Range("B45").Value & Sheets("INPUTS").Range("E11").Value & ".xls" The compiler error message I am getting says "object variable or with block variable not set." I am pretty sure this statement had been working but somehow now is not. I have had it show me that variable, which is entered thru an Input Box and it is what it's supposed to be. But the command is not working. Does this error message tell anyone anything? Then, this subroutine calls another subroutine where I use a similar statement to try to open a file. In this case, the statement that is crashing is: myFilename = StrMGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".L00" It was not an XLS file and that caused some confusion but it now seems evident that the real problem is that this same strMGR_SHORT_NAME is now blank and so is not automatically being "seen' by the called subroutine. So, the solution for this seems to be merely to pass the result of the INPUTBox along and so the question is, shouldn't a calling subroutine pass its definitions along to a subroutine it calls? And, if not, how do I get it to do so? Thanks, Grace |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still filename problem
Your use of subroutines in your answer confuses me. I guess good VBA
programmers use them a lot. But after reading the VBA help section you mentioned, I assume that all I need to do is something like: Dim PublicstrSHORT_MGR_NAME as string and that should make that variable available to all modules. Is that right? Do I have to declare it in each module or just any module? If just one module, does it have to be in Module 1, in order to be available in Module 2 and not vice versa (or are module names just names)? In any event, I tried what I've suggested above and it didn't help. It still is not recognized in Module 2. By the way the help section indicated there should be a space after Public but when I tried that, the compiler gave me syntax errors. Please help, anyone! Thanks, Grace "JE McGimpsey" wrote in message ... The answer to your second question is easy, and you should look up "Understanding Scope and Visibility" in XL/VBA Help. Variables declared within a module are only visible within the module: Public Sub foo() Dim vTest As Variant vTest = "test" bar End Sub Public Sub bar() MsgBox vTest End Sub In this case, bar() will display an empty message box. Incidentally, this is a superb reason to put "Option Explicit" at the top of your modules (choose Preference/Editor in the VBE, and check the Require variable declaration checkbox to make this automatic). If you'd had that, you would have gotten a compile error - variable not declared in bar(). To pass variables on, you could declare them globally (at the top of the module), which is a widely used technique, but one that sometimes gets beginning coders in trouble when two procedures operate on the same global. Dim vTest As Variant Public Sub foo() vTest = "test" bar End Sub Public Sub bar() MsgBox vTest End Sub Or you could explicitly pass them as an argument: Public Sub foo() Dim vTest As Variant vTest = "test" bar vTest End Sub Public Sub bar(ByRef vTest2 As Variant) MsgBox vTest End Sub this time, bar() will display "test" in the message box. In article , "Grace" wrote: I am sorry to repost this but it looks like too much time is passed and the solution never came. I have two statements that involve a string called "strMGR_SHORT_NAME that are having problems. In the first case, the statement is fname = StrMGR_SHORT_NAME & Sheets("INPUTS").Range("B45").Value & Sheets("INPUTS").Range("E11").Value & ".xls" The compiler error message I am getting says "object variable or with block variable not set." I am pretty sure this statement had been working but somehow now is not. I have had it show me that variable, which is entered thru an Input Box and it is what it's supposed to be. But the command is not working. Does this error message tell anyone anything? Then, this subroutine calls another subroutine where I use a similar statement to try to open a file. In this case, the statement that is crashing is: myFilename = StrMGR_SHORT_NAME & Sheets("Inputs").Range("E11").Value & "SUMPRF" & ".L00" It was not an XLS file and that caused some confusion but it now seems evident that the real problem is that this same strMGR_SHORT_NAME is now blank and so is not automatically being "seen' by the called subroutine. So, the solution for this seems to be merely to pass the result of the INPUTBox along and so the question is, shouldn't a calling subroutine pass its definitions along to a subroutine it calls? And, if not, how do I get it to do so? Thanks, Grace |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still filename problem
Yup, I was sloppy in my terminology.
You can make a variable available to all modules by declaring them with the Public keyword: Public strSHORT_MGR_NAME As String You got a syntax error because Public is not valid after a Dim By removing the space, you declared a variable visible only within the module wi the name "PublicstrSHORT_MGR_NAME" From XL/VBA Help on "Dim": Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure. This is an excellent reason that you should go to Tools/Options in the VBE, and in the Editor pane, check the Require Variable Declaration checkbox. If you'd had that checked, you would have gotten an error when compiling telling you that the variable "strSHORT_MGR_NAME" was not declared, even within the module. In article , "Grace" wrote: Your use of subroutines in your answer confuses me. I guess good VBA programmers use them a lot. But after reading the VBA help section you mentioned, I assume that all I need to do is something like: Dim PublicstrSHORT_MGR_NAME as string and that should make that variable available to all modules. Is that right? Do I have to declare it in each module or just any module? If just one module, does it have to be in Module 1, in order to be available in Module 2 and not vice versa (or are module names just names)? In any event, I tried what I've suggested above and it didn't help. It still is not recognized in Module 2. By the way the help section indicated there should be a space after Public but when I tried that, the compiler gave me syntax errors. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still filename problem
Ok, this is now solved, by assigning it to a cell.
Thanks, Grace "Grace" wrote in message ... I use this variable plus a number of others I enter through InputBox to create filenames that I will later open (after I, knowingly, give them just the right names). What you just told me helped but I was still encountering problems in other modules. Then, it occurred to me that this variable was the only one giving me problems. For all the other variables, I assigned there values to a cell in the workbook. This seems like a better way to make something "public". Once it is in a cell, any module or subroutine should be able to get that cell's value. Do you agree? In any event, the happy ending has not yet quite arrived. For some reason, even thought he macro's msgbox shows that strMGR_SHORT_NAME has the right result: Sheets("INPUTS").Range("C29").Value = StrMGR_SHORT_NAME is not successfully populating cell C29. This procedure worked for all the other variables. I just started a new thread called "help anyone". If you could review that, I would appreciate it. Thanks, Grace "JWolf" wrote in message .. . Put it immediately above the option explicit statement, any module, but sometimes its easier to keep track of if you put all your subs using the public variable in one module (not always possible). The public statement is equivalent to a dim statement within a module, thus public variables can't be declared inside the sub, they already exist. Grace wrote: Ok, thanks for sticking with me. I had already inserted the Option Explicit at the top of each module and had set it up as the default, too. I got that advice two days ago, I think, from several folks. It is still not clear to me where I should declare it public. Can it be at the top of any one module? Does that mean immediately below the Option Explicit Statement? Also, should it be declared after it is dimensioned (without the use of public there)? I have tried that and am currently getting an "ambiguous name detected". Thanks, Grace "JE McGimpsey" wrote in message ... Yup, I was sloppy in my terminology. You can make a variable available to all modules by declaring them with the Public keyword: Public strSHORT_MGR_NAME As String You got a syntax error because Public is not valid after a Dim By removing the space, you declared a variable visible only within the module wi the name "PublicstrSHORT_MGR_NAME" From XL/VBA Help on "Dim": Variables declared with Dim at the module level are available to all procedures within the module. At the procedure level, variables are available only within the procedure. This is an excellent reason that you should go to Tools/Options in the VBE, and in the Editor pane, check the Require Variable Declaration checkbox. If you'd had that checked, you would have gotten an error when compiling telling you that the variable "strSHORT_MGR_NAME" was not declared, even within the module. In article , "Grace" wrote: Your use of subroutines in your answer confuses me. I guess good VBA programmers use them a lot. But after reading the VBA help section you mentioned, I assume that all I need to do is something like: Dim PublicstrSHORT_MGR_NAME as string and that should make that variable available to all modules. Is that right? Do I have to declare it in each module or just any module? If just one module, does it have to be in Module 1, in order to be available in Module 2 and not vice versa (or are module names just names)? In any event, I tried what I've suggested above and it didn't help. It still is not recognized in Module 2. By the way the help section indicated there should be a space after Public but when I tried that, the compiler gave me syntax errors. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
set filename to <filename-date on open | Excel Worksheet Functions | |||
Filename problem | Excel Programming | |||
Saving filename same as import filename | Excel Programming | |||
Quote in filename causes problem with Application.Run | Excel Programming |