Using a variable calculated in a procedure in another procedure.

[Trinidad3]

I hope someone can help me. I have created a simple task which does not require using 2 seperate Subs to get it to work, however i hope someone can show me what how to get this to work.
A B C
1 Qty Price Category
2 500 3.75 Z

ABove is the spread sheet layout, with 3 variables, Qty Price and Category.

I want the answer in cell D2 to be equal to qty multiplied by price, and then if Category is "Z" then multiplied by 1 otherwise in all other cases multiplied by 2.

that is easy enough but i want to use two procedures as follows to teach myself how to use variables created in one procedure and use the result in another:

Sub TotalValue()
Call CategoryProc
Cells(2, 4) = Cells(2, 1) * Cells(2, 2) * dd
End Sub

Sub CategoryProc()
If Cells(2, 3) = "Z" Then
dd = 1
Else
dd = 2
End If
End Sub

thanks to all in advance.

BP

[Andrew-R]

The simple answer is don't use variables from one procedure in another. If you want to call a seperate bit of code to do a calculation and return a value then you should use a function, e.g.

Please Login or Register  to view this content.

[Trinidad3]

Thank you so very very much

[Andrew-R]

No problems - spend a bit of time getting to grips with functions, because they are incredibly useful.

This, for example, is a better implementation of your code...

Please Login or Register  to view this content.

[oldchippy]

Hi Trinidad3,

Welcome to the forum, please take time to read the forum rules - in particular this one

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[Andrew-R]

Oh, one last thing - I feel a tiny bit guilty about not answering your original question, so ... in a code module you can declare global variables, which can be used by all subs in that module, so you could make your original code work with just one more line...

Please Login or Register  to view this content.

However, while they have their uses, most of the time global variables just stink of sloppy coding, so they're really best avoided.

[Trinidad3]

Thanks ANdrew, Thanks....you have opened up a whole new world for me...will neaten up my code and make it less confusing. I use functions extensively...so its the other two examples helped me a lot.

OldChippy. Sorry...I am going to the rules right away to read all properly. I assumed the rules would just be standard like "no vulgarity" etc etc.

thanks again

[snb]

But in this case I wouldn't use a function.

Please Login or Register  to view this content.

[Andrew-R]

I think the code was just an example to illustrate the question, rather than being an actual bit of a functional macro.