Re: $$Excel-Macros$$ Passing Variable to another function

[Paul Schreiner]

 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

[hanumant shinde]

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

[Srinivasulu Reddy Yarasi]

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

[Paul Schreiner]

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

[manhar prajapati]

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

[hanumant shinde]

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

[Paul Schreiner]

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.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.facebo