Re: $$Excel-Macros$$ Passing Variable to another function
It seems you have Hijacked another discussion thead (begun by Nihal on Feb 5, 2011) If you wish to begin a new query, please start your own topic. Especially as this has absolutely nothing to do with passing variables to functions! Paul From: Srinivasulu Reddy Yarasi To: excel-macros@googlegroups.com Sent: Thu, February 10, 2011 9:14:09 PM Subject: Re: $$Excel-Macros$$ Passing Variable to another function Hi Manhar, Use the following formula, =LEFT(C3,FIND("'",C3,1)-1)+VALUE(MID(C3,FIND("'",C3,1)+1,FIND("""",C3,1)-FIND("'",C3,1)-1))/12 Above formula is for the value entered in Inches in cell C3 and always the value is expected in Feet(') and Inches("), the forumula also ignores the multiple spaces beteem the Feet and Inches value. Attaches is the ssheet with solution formula filled with yellow colour. Regards SeenuYarasi On Thu, Feb 10, 2011 at 12:21 PM, manhar prajapati wrote: Dear All >I require your urgent help for attach file. > >Please find attach file for calculation of Square meter form Inches to >Centimeter. >I want to a formula which convert inches in to Centimeter. >Example >10' 3'' is equal to 10.25 for Calculation of Square Feet. >Thanks and regards >Manhar Prajapati >-- >-- > >Some important links for excel users: >1. Follow us on TWITTER for tips tricks and links : >http://twitter.com/exceldailytip >2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 >3. Excel tutorials at http://www.excel-macros.blogspot.com/ >4. Learn VBA Macros at http://www.quickvba.blogspot.com/ >5. Excel Tips and Tricks at http://exceldailytip.blogspot.com/ > >To post to this group, send email to excel-macros@googlegroups.com > ><><><><><><><><><><><><><><><><><><><><><><> >Like our page on facebook , Just follow below link >http://www.facebook.com/discussexcel > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Passing Variable to another function
Hi Paul, thanks a lot dude. your theory helped me i ll try your code too today. i will let u know. From: Paul Schreiner To: excel-macros@googlegroups.com Sent: Thu, 10 February, 2011 6:26:50 PM Subject: Re: $$Excel-Macros$$ Passing Variable to another function First of all: Dim Workingday as String does not declare the variable as "Global". It merely makes it a "module-level" variable. It would NOT be available to other modules or to sheet modules. the syntax for declaring a Global variable is: Global WorkingDay as String or: Public WorkingDay as String Second, I think the "qualifier" you're looking for is "ByRef" Try the following code: - Option Explicit Global workingday Sub calling() If Not (calculateworkingday(workingday)) Then MsgBox "WorkingDay not set" Else MsgBox workingday End If End Sub Public Function calculateworkingday(ByRef abcd) As Boolean abcd = "test" calculateworkingday = True End Function - Note: if you use Global or Public, then when the sub/function execution is complete, the variable still has a value! That means that the second time you run it, the value is STILL THERE! Now, if you want the value CLEARED once macros are no longer running, then you can still use Dim. Paul From: hanumant shinde To: excel-macros@googlegroups.com Sent: Thu, February 10, 2011 12:48:05 AM Subject: Re: $$Excel-Macros$$ Passing Variable to another function Hi Paul, Sorry for late reply as i was out of town i couldnt check this out. Thanks a lot paul for your detail explanation.and really sorry for creating confusion. my que is "Can a variable be passed to a function without initializing it" and getting their values. i have explained in detail below. 1. that variable is declared as global variable but it has not initialised anywer. 2. the called function work on that global variable which is passed to the called fucntion and changes its value but that function jus return "true" or "False" nothing else. then how that variable is assigned some value. please find the example below dim workingday as some data type "workingday" this is declared as global variable. Sub calling () if not (calculateworkingday(workingday)) then 'here we are passing that variable to some function . end sub 'calculateworkingday this is called function public function calculateworkingday(abcd as some datatype) boolean ' in this code we use 'abcd' variable and not that actual global variable. 'using this 'abcd' variable we change the value of 'abcd' 'this function jus return 'true' or 'false' then how that acual value is changed? is it bcoz that variable is global? i tried same kind of thing i.e. passing global variable without initialising it but i got error. calculateworkingday = true end function ____ From: Paul Schreiner To: excel-macros@googlegroups.com Sent: Mon, 7 February, 2011 11:41:24 PM Subject: Re: $$Excel-Macros$$ Passing Variable to another function Your question is a bit confusing: You want to know "if we can pass the variables without initializing them to any other function and get their values from that called function " "Initializing" a variable is not the same as "Declaring" a variable. Dim workingday as Integer "Declares" the variable "WorkingDay" as type Integer. Workingday = 0 "Initializes" the variable, or sets the "Initial Value" to 0. When a variable is first Declared, it's value is usually "null". - If your question really is: "Can a variable be passed to a function without initializing it", then the answer depends on what the function does with the variable. If the function uses a statement like: If WorkingDay = 0 then it may produce an error if WorkingDay is null, because the resulting statement is: If = 0 then If your question is "if we can pass the variables without DECLARING them": The answer here is based on the understanding of Variable Declarations. First of all, using: Option Explicit will cause the compiler to require that ALL variables be declared before being used. But if Option Explicit is NOT used: Rule #1: to a compiler, ALL variables are "DECLARED". The question is: when/where are they declared? You can declare them Explicitly by using: Dim NewVariable but WHERE this statement is placed is important too! If you have a macro/Sub called "Calling", and use: Dim VarModLevel Sub Calling() Dim VarSubLevel then the Va
Re: $$Excel-Macros$$ Passing Variable to another function
Hi Manhar, Use the following formula, =LEFT(C3,FIND("'",C3,1)-1)+VALUE(MID(C3,FIND("'",C3,1)+1,FIND(,C3,1)-FIND("'",C3,1)-1))/12 Above formula is for the value entered in Inches in cell C3 and always the value is expected in Feet(') and Inches("), the forumula also ignores the multiple spaces beteem the Feet and Inches value. Attaches is the ssheet with solution formula filled with yellow colour. Regards SeenuYarasi On Thu, Feb 10, 2011 at 12:21 PM, manhar prajapati < prajapati.man...@gmail.com> wrote: > Dear All > I require your urgent help for attach file. > > Please find attach file for calculation of Square meter form Inches to > Centimeter. > > I want to a formula which convert inches in to Centimeter. > > Example > > 10' 3'' is equal to 10.25 for Calculation of Square Feet. > > Thanks and regards > Manhar Prajapati > > -- > > -- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : > http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/discussexcel > -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel Inches.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Passing Variable to another function
First of all: Dim Workingday as String does not declare the variable as "Global". It merely makes it a "module-level" variable. It would NOT be available to other modules or to sheet modules. the syntax for declaring a Global variable is: Global WorkingDay as String or: Public WorkingDay as String Second, I think the "qualifier" you're looking for is "ByRef" Try the following code: - Option Explicit Global workingday Sub calling() If Not (calculateworkingday(workingday)) Then MsgBox "WorkingDay not set" Else MsgBox workingday End If End Sub Public Function calculateworkingday(ByRef abcd) As Boolean abcd = "test" calculateworkingday = True End Function - Note: if you use Global or Public, then when the sub/function execution is complete, the variable still has a value! That means that the second time you run it, the value is STILL THERE! Now, if you want the value CLEARED once macros are no longer running, then you can still use Dim. Paul From: hanumant shinde To: excel-macros@googlegroups.com Sent: Thu, February 10, 2011 12:48:05 AM Subject: Re: $$Excel-Macros$$ Passing Variable to another function Hi Paul, Sorry for late reply as i was out of town i couldnt check this out. Thanks a lot paul for your detail explanation.and really sorry for creating confusion. my que is "Can a variable be passed to a function without initializing it" and getting their values. i have explained in detail below. 1. that variable is declared as global variable but it has not initialised anywer. 2. the called function work on that global variable which is passed to the called fucntion and changes its value but that function jus return "true" or "False" nothing else. then how that variable is assigned some value. please find the example below dim workingday as some data type "workingday" this is declared as global variable. Sub calling () if not (calculateworkingday(workingday)) then 'here we are passing that variable to some function . end sub 'calculateworkingday this is called function public function calculateworkingday(abcd as some datatype) boolean ' in this code we use 'abcd' variable and not that actual global variable. 'using this 'abcd' variable we change the value of 'abcd' 'this function jus return 'true' or 'false' then how that acual value is changed? is it bcoz that variable is global? i tried same kind of thing i.e. passing global variable without initialising it but i got error. calculateworkingday = true end function ____ From: Paul Schreiner To: excel-macros@googlegroups.com Sent: Mon, 7 February, 2011 11:41:24 PM Subject: Re: $$Excel-Macros$$ Passing Variable to another function Your question is a bit confusing: You want to know "if we can pass the variables without initializing them to any other function and get their values from that called function " "Initializing" a variable is not the same as "Declaring" a variable. Dim workingday as Integer "Declares" the variable "WorkingDay" as type Integer. Workingday = 0 "Initializes" the variable, or sets the "Initial Value" to 0. When a variable is first Declared, it's value is usually "null". - If your question really is: "Can a variable be passed to a function without initializing it", then the answer depends on what the function does with the variable. If the function uses a statement like: If WorkingDay = 0 then it may produce an error if WorkingDay is null, because the resulting statement is: If = 0 then If your question is "if we can pass the variables without DECLARING them": The answer here is based on the understanding of Variable Declarations. First of all, using: Option Explicit will cause the compiler to require that ALL variables be declared before being used. But if Option Explicit is NOT used: Rule #1: to a compiler, ALL variables are "DECLARED". The question is: when/where are they declared? You can declare them Explicitly by using: Dim NewVariable but WHERE this statement is placed is important too! If you have a macro/Sub called "Calling", and use: Dim VarModLevel Sub Calling() Dim VarSubLevel then the Variable VarSubLevel is available ONLY within the Subroutine. While VarModLevel is available to all subs in the Module. However, it would NOT be available to Userforms and when macros complete. Now, OUTSIDE of a subroutine, you can use: Global VarGlobal or Public VarPublic These variables are available until Excel exits. a second
Re: $$Excel-Macros$$ Passing Variable to another function
Dear All I require your urgent help for attach file. Please find attach file for calculation of Square meter form Inches to Centimeter. I want to a formula which convert inches in to Centimeter. Example 10' 3'' is equal to 10.25 for Calculation of Square Feet. Thanks and regards Manhar Prajapati -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel inches.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Passing Variable to another function
Hi Paul, Sorry for late reply as i was out of town i couldnt check this out. Thanks a lot paul for your detail explanation.and really sorry for creating confusion. my que is "Can a variable be passed to a function without initializing it" and getting their values. i have explained in detail below. 1. that variable is declared as global variable but it has not initialised anywer. 2. the called function work on that global variable which is passed to the called fucntion and changes its value but that function jus return "true" or "False" nothing else. then how that variable is assigned some value. please find the example below dim workingday as some data type "workingday" this is declared as global variable. Sub calling () if not (calculateworkingday(workingday)) then 'here we are passing that variable to some function . end sub 'calculateworkingday this is called function public function calculateworkingday(abcd as some datatype) boolean ' in this code we use 'abcd' variable and not that actual global variable. 'using this 'abcd' variable we change the value of 'abcd' 'this function jus return 'true' or 'false' then how that acual value is changed? is it bcoz that variable is global? i tried same kind of thing i.e. passing global variable without initialising it but i got error. calculateworkingday = true end function ____ From: Paul Schreiner To: excel-macros@googlegroups.com Sent: Mon, 7 February, 2011 11:41:24 PM Subject: Re: $$Excel-Macros$$ Passing Variable to another function Your question is a bit confusing: You want to know "if we can pass the variables without initializing them to any other function and get their values from that called function " "Initializing" a variable is not the same as "Declaring" a variable. Dim workingday as Integer "Declares" the variable "WorkingDay" as type Integer. Workingday = 0 "Initializes" the variable, or sets the "Initial Value" to 0. When a variable is first Declared, it's value is usually "null". - If your question really is: "Can a variable be passed to a function without initializing it", then the answer depends on what the function does with the variable. If the function uses a statement like: If WorkingDay = 0 then it may produce an error if WorkingDay is null, because the resulting statement is: If = 0 then If your question is "if we can pass the variables without DECLARING them": The answer here is based on the understanding of Variable Declarations. First of all, using: Option Explicit will cause the compiler to require that ALL variables be declared before being used. But if Option Explicit is NOT used: Rule #1: to a compiler, ALL variables are "DECLARED". The question is: when/where are they declared? You can declare them Explicitly by using: Dim NewVariable but WHERE this statement is placed is important too! If you have a macro/Sub called "Calling", and use: Dim VarModLevel Sub Calling() Dim VarSubLevel then the Variable VarSubLevel is available ONLY within the Subroutine. While VarModLevel is available to all subs in the Module. However, it would NOT be available to Userforms and when macros complete. Now, OUTSIDE of a subroutine, you can use: Global VarGlobal or Public VarPublic These variables are available until Excel exits. a second way to declare variables is when passing a value to a function: Function Calling(LocalVar) will Declare a new variable "LocalVar" that is only available within the function. Now.. if you are NOT using Option Explicit, then variables can be used WITHOUT first declaring them. This means that when you use: IntVar = 100 The variable IntVar is "automatically" declared as if you used: Dim IntVar as Integer However, more often, Excel chooses to declare the variable as type Variant (Dim IntVar as Variant) At the same time, if you use: IntVar = "100", then it gets declared as a STRING variable as if you used: Dim IntVar as String. subsequent attempts to use is as a number: IntVar = IntVar + 1 will produce an error. --- So.. to answer your actual question: "if we can pass the variables without initializing them to any other function and get their values from that called function" The problem in your example is that your function: CalculateWorkingDay(abcd as some datatype) abcd is declared as a specifid data type. That means that the variable passed TO the function: "WorkingDay" in: CalculateWorkingDay(WorkingDay) must be the same data type. To test this type of thing, try this: Option Explicit Global
Re: $$Excel-Macros$$ Passing Variable to another function
Your question is a bit confusing: You want to know "if we can pass the variables without initializing them to any other function and get their values from that called function " "Initializing" a variable is not the same as "Declaring" a variable. Dim workingday as Integer "Declares" the variable "WorkingDay" as type Integer. Workingday = 0 "Initializes" the variable, or sets the "Initial Value" to 0. When a variable is first Declared, it's value is usually "null". - If your question really is: "Can a variable be passed to a function without initializing it", then the answer depends on what the function does with the variable. If the function uses a statement like: If WorkingDay = 0 then it may produce an error if WorkingDay is null, because the resulting statement is: If = 0 then If your question is "if we can pass the variables without DECLARING them": The answer here is based on the understanding of Variable Declarations. First of all, using: Option Explicit will cause the compiler to require that ALL variables be declared before being used. But if Option Explicit is NOT used: Rule #1: to a compiler, ALL variables are "DECLARED". The question is: when/where are they declared? You can declare them Explicitly by using: Dim NewVariable but WHERE this statement is placed is important too! If you have a macro/Sub called "Calling", and use: Dim VarModLevel Sub Calling() Dim VarSubLevel then the Variable VarSubLevel is available ONLY within the Subroutine. While VarModLevel is available to all subs in the Module. However, it would NOT be available to Userforms and when macros complete. Now, OUTSIDE of a subroutine, you can use: Global VarGlobal or Public VarPublic These variables are available until Excel exits. a second way to declare variables is when passing a value to a function: Function Calling(LocalVar) will Declare a new variable "LocalVar" that is only available within the function. Now.. if you are NOT using Option Explicit, then variables can be used WITHOUT first declaring them. This means that when you use: IntVar = 100 The variable IntVar is "automatically" declared as if you used: Dim IntVar as Integer However, more often, Excel chooses to declare the variable as type Variant (Dim IntVar as Variant) At the same time, if you use: IntVar = "100", then it gets declared as a STRING variable as if you used: Dim IntVar as String. subsequent attempts to use is as a number: IntVar = IntVar + 1 will produce an error. --- So.. to answer your actual question: "if we can pass the variables without initializing them to any other function and get their values from that called function" The problem in your example is that your function: CalculateWorkingDay(abcd as some datatype) abcd is declared as a specifid data type. That means that the variable passed TO the function: "WorkingDay" in: CalculateWorkingDay(WorkingDay) must be the same data type. To test this type of thing, try this: Option Explicit Global TestVar Sub Testing() TestVar = 1 MsgBox "Testing: TestVar = " & TestVar Test2 MsgBox "Testing2: TestVar = " & TestVar End Sub Sub Test2() Dim TestVar, stat TestVar = 2 MsgBox "Test2: TestVar = " & TestVar stat = TestFunction(TestVar) MsgBox "Test2a: TestVar = " & TestVar End Sub Function TestFunction(TestVar2) TestVar2 = TestVar2 + 1 MsgBox "Function:" & Chr(13) & "TestVar = " & TestVar & Chr(13) & "TestVar2 = " & TestVar2 End Function does ANY of this answer your actual question? Maybe I'd need to know what you're trying to accomplish? Paul From: hanumant shinde To: excel macros Sent: Sat, February 5, 2011 2:52:34 PM Subject: $$Excel-Macros$$ Passing Variable to another function Hi friends, I wanna know if we can pass the variables without initializing them to any other function and get their values from that called function please find the below example. which is similar to that of i have seen. dim workingday as some data type Sub calling () if not (calculateworkingday(workingday)) then . end sub public function calculateworkingday(abcd as some datatype) boolean some code here which give some value to abcd calculateworkingday = true end function -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogsp
$$Excel-Macros$$ Passing Variable to another function
Hi friends, I wanna know if we can pass the variables without initializing them to any other function and get their values from that called function please find the below example. which is similar to that of i have seen. dim workingday as some data type Sub calling () if not (calculateworkingday(workingday)) then . end sub public function calculateworkingday(abcd as some datatype) boolean some code here which give some value to abcd calculateworkingday = true end function -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel