VBA Help - This should be an easy one :-)

Posted by Dan on December 11, 2001 12:09 PM

I am experienced in VB, but have not worked with VBA for Excel much. I am trying to find a way to change the format of a number in a field.

What I want to be able to do is turn a value of pennies intot he corresponding dollars/quarters/dimes/nickles/pennies value.

I don't need help with the formula to convert, I need help with getting the formula integrated into Excel.

What I was thinking was make a function, so it could be used much like the ADD function. I want to be able to have a section where the user could shut off this feature. I did that part by adding a field that only accepts the values Yes of No. I can then reference that field in the function to check to see what the the value is. If it is yes, quit the function and return the value in pennies. If it is no, run the function and return the value in the seperated by change.

So, how do I do this in excel? I am sure I can figure it out as soon as someone gives me a point in the right direction. Thanks for your help.

Posted by CMorrigu on December 11, 2001 12:13 PM

It's not quite clear to me the specifics you want, but based on what I understand of what you want, I'd probably call it like this:

activecell.value = numformat(activecell.value)Shadow Source

Posted by Dan on December 11, 2001 12:21 PM

I am sorry if that wasn't clear.

Lets say a field has the value if 141.

I want a function that will change this field to $1 1q 1d 1n 1p. Where q is for quarter, d is for dime and so on.

I know the programming logic required to do this, but I don't know how to do it in VB.

So, here is more spcific question.
-How do I get a function to take the value in a field, manipulate the value and then return it to the field?

Posted by CMorrigu on December 11, 2001 12:52 PM

Then what I posted still applies. Activecell.value will give the value of the active cell. Or cells(1,1).value will give the value of A1. You can set the value with the same reference.

Shadow Source

Posted by Dan on December 11, 2001 1:20 PM

Thanks for your help. I think that has solved my problem.

If not, I'll be back :)