HiI'm setting up a function in VBA.The problem is that I would like to make some of the variables, that my function takes as inputs, public.Eg. in the functionFunction Price(TTM as double, margin as double)I would like to use the variable margin in another module.I have tried "Function Price(TTM as double, Public margin as double)" but it does not work.Neither does declaring the variable as public at module level (before the function statement)A workaround would be to declare another public variable and then set this variable equal to the input variable, but its kind of messy.Anyone who knows if its possible to make the variables in the function statement public in a more tidy way?Thanks

u need to read a bit about variable scopes/visibility.i didnt do any VBA for quite some time, but it seems that if u wish to declare a variable public (global?), then u shouldnt put it in arg list of function, because the function will create a new variable with function scope, provided that i understand what VBA is doing here

jawa....nah, vb passes arguments by reference; no copy unless you specify Function MyFunc(ByVal x as Double).kommakul...i think you have a misundersanding somewhere... the margin variable only exists within the function price. the actual Double that the "margin" variable contains may change if two different sections of code call that function and pass it different Doubles. if you want a "margin" variable to exist outside the function, you have to declare it outside the function (public or private as you wish), in which case you don't need to pass it as an argument to the price function anyway because Price can access it as can any other function.

you can use global variables ... except you do not code quite careful (which is notlikely otherwise you would not have asked) you will get terrible dynamical code

QuoteOriginally posted by: samyonezjawa....nah, vb passes arguments by reference; no copy unless you specify Function MyFunc(ByVal x as Double).i didnt say anything about copying, i said about scopes. it's important to understand the scope where the variable is defined.it's not a good idea (usually) to do what he wants to do, i.e. declare global variables.

sorry, my bad; i misread what you were saying & read "will create a new variable" as "will copy the variable"kommakul; also read about "encapsulation" in context of object oriented programming to understand why in general declaring a variable as public is usually a bad idea.

Thanks for your input. I'll look into some of the concepts.QuoteOriginally posted by: samyonezif you want a "margin" variable to exist outside the function, you have to declare it outside the function (public or private as you wish), in which case you don't need to pass it as an argument to the price function anyway because Price can access it as can any other function.Samyonez: I call the function from a worksheet, so the margin variable only enters through the function call. I know it would be straightforward if the function was called from within VBA.I would like to calculate the function for different margins from the worksheet, but in order to do so I need the value of the margin variable in another module. That's why I would like it to be public.The workaround I stated earlier works and another approach would be to include everything in the same module.But I just became curious, if the other way around would be possible in any way, but it seems it isn't.Thanks for your replies.

A variable name is an alias for the area of memory that VBA and Excel have decided to put the data.A global variable is in a fixed, known, unique place, and by making it global, you tell VA that you want it's name to be visible everywhere.But a parameter is a stack variable. It does not have a specific location.Consider the following function:function Factorial (x as double) as doubleDim StupidVariableThatJustHangsAround as doubleif x =0 or x =1 then factorial = 1elseFactorial = x * Factorial (x-1)END IFend functionThis function will call itself, multiplying x as it goes, and you will have many x's each with a different variable.Also you will have many instances of StupidVariableThatJustHangsAround, the local variable.You can make StupidVariableThatJustHangsAround static, and so only have one copy, but you can't have a static parameter.There is a way of doing this, but it is so quite overwhelmingly ugly that I don't recommend it.

I think you are missing basic principles of encapsulation here.As a general rule you should not rely on the public variables, but rather set up properties. This will give you more control over how properties are assigned values and how they return values. Property enables you to validate the data taht is passed to the object and you can also perfrom some actions if you want when you pass the data to the property. Property procedures should be your interface between private variables of you module and the outside worldSo in your case you have a module "SomeModule" Within this module you have a function Price(TTM as double, margin as double) that takes two parameters: TTM and marginYou want to reuse parameter margin in other module. The right way to do that is to set up a "Margin" property within your "SomeModule" module. To do that you have to declare a private variable dMargin and then you have to create accessor and modifier properties for this private variable (sometimes they are called getter and setter)this would look likePrivate dMargin As DoubleProperty Set Margin(ByRef Margin As Double) Set dMargin = MarginEnd PropertyProperty Get Margin() As Double Set Margin = dMarginEnd PropertyYou want to use this variable in the other module right?? To do that you have to set Margin property equal to the value of the parameter margin that you pass to you Price function.Public Function Price(TTM As Double, Margin As Double) some other code goes here 'This will set Margin property Me.Margin = MarginEnd FunctionNow to get the value of the margin parameter in the other Module all you have to do is 1) instantiate "SomeModule" module 2) execute Price() function 3) get the value of the Margin property This would look something like thatDim dMarginThatWeShallGetFromSomeModule as DoubleDim objSomeModule As SomeModuleSet objSomeModule = New SomeModuleobjSomeModule.Price(TTM, margin)Set dMarginThatWeShallGetFromSomeModule = objSomeModule.MarginDo not forget scope of private variable and objects is worksheet, the scope of public - workbookProperty Let - used to assign a value to a propertyProperty Set - used to assign an object to a propertyProperty Get - used to return the value or the object reference in a property to an outside world (if you ever want to get something out of you class module you should always go through this property)