Simple macro help, need to create a formula/function

Hey guys,

I'm new to VBA/programming and I am trying to create my own formula in excel for which I believe VBA is required. I often have to manually convert between different units of oil/gas and would like to install a few custom formulas to be able to make it easier. One example is the conversion from Billions of Barrels of Oil (Bbl) to Millions of Cubic Feet (Mcf). The conversion factor for that is 6, so simply 6*Bbl = Mcf. How could I go about entering that in VBA so that when I wanted to quickly convert Bbl into Mcf I could just enter in a cell = x*Bbl_to_Mcf? (The idea being I type the "Bb" and the rest of the formula name auto fills). Also, should I make it as a plugin? (Not entirely sure what that is though)

Thanks for the help.

Hi

How about just using a defined name?

Formulas, Name Manager, New, name: bbl_to_mcf, refers to: =6
Then if you have the number you want to convert in A1, select your cell and go =a1*bbl and then select the defined name that will appear.

HTH

rylo

Thanks for the quick reply. Basically let's say in cell A1 I have the value 10 representing 10 billions of barrels of oil (Bbl), I want to be able to click a blank cell and input "=A1*Bbl_to_Mcf" which would yield 60 (Mcf). Hopefully that's clear? Thanks for the help.

Originally Posted by Xer2

Rylo's suggestion does exactly what you need. Define a name (bbl_to_mcf) that equals 6 and then use the formula you are talking about:

Formula:

Please Login or Register  to view this content.

Yeah this should work fine but how can I get it to insert the selected cell into the formula? Say it's not in cell A1, but in C4, how do I set that up?

Hi

Unless I'm missing something it would be =C4*bbl_to_mcf

Of course, you can select a cell, put in the =, use the mouse to select C4, then continue with the *bbl_to_mcf.

rylo

I need help setting up the initial formula because it can't be just "=A1*6", because the value might not be in cell A1...

HI

Can you document exactly what steps you want to be able to make, where your data is, what result would eventuate from what you envisage. I'm not at all sure now what you are trying to achieve.

rylo

I just figured it out, was more simple than I thought, thanks guys!

This ended up working for me, do you know if there is a way to export/send a list or group of defined names? Like say if I make 20 unique names/formulas, can I save those as an add in or something so that someone elsewhere won't have to go through the trouble of setting it up as well? Thanks

Ok, I still think it would be better to set this up as a macro so that I could send the forumlas to others as an add in. Could someone help me figure out how I'd go about doing that?