Simple math entered into a text box and calculated

[swas]

Hi,



I have a stocktake form where amounts are entered into from a manual sheet.

Quite often we have different product box sizes, ie 12 / carton, 24 / carton, 30 / carton or even units.

Our stock is only entered as units, so 10 cartons of 24 + 2 cartons of 30 would be 300 units.

This has to be manually calculated each time, and the units entered.

I'd like to be able to enter "=10*24+2*30" into a textbox and have have a function to calculate automatically, ie. like a simple calculator, or a spreadsheet cell. I would detect the "=" and take the remainder of the string to be the equation.

I'm experienced in VBA and could probably parse the string and calculate - in proper order (/* before +- etc...) in code. But then I'm thinking, can VBA do this for me already?

Example, in debug immediate I can type ?10*24+2*30 and get the correct 300 answer. I want to be able to do this in code.


Hopefully I have explained this clearly enough, and thanks in advance for comments.


swas

[Micron]

VBA: Me.txtCount = 10*24+2*30
Forget that - you didn't mean to enter the formula in vba. So txtCount = Eval(txtCount.Text) I'd use the AfterUpdate event for the control unless you need to use a command button for some reason.
Easier if you forget the = sign unless you need that as some kind of check.

[swas]

Thanks Micron.

You are setting the text box value, which isn't what I am wanting.

I want to enter "= 10*24+2*30" into a textbox as a string and get 300 as a value to then use (Assign to a field / use in code, whatever).

Or as a function
strTemp="= 10*24+2*30": debug.print MyCalc(strTemp)
Returns 300

Or without the "="
strTemp="10*24+2*30": debug.print MyCalc(strTemp)


Sorry if not explained clearly.

Thanks
swas

[Micron]

I don't think you saw my post edits.

[swas]

Ahhh no I didn't.

That's perfect. I knew VBA should be able to do it...

Re dropping the = sign, I agree.


Thanks heaps

swas

[Micron]

HTH. Maybe mark this one as solved if it works for you?
In case it matters, I tested that on an unbound control and that might be an issue. If you want the control to be bound and be a number field, you won't be able to do this. You can use an unbound control and update the table field within your event code though, and be able to keep the field as a number data type.

[swas]

Yes mark as solved.
Re bound number fields etc... I'll figure a workaround.
It's in a continuous form so maybe I'll put an unbound textbox over the bound control, and if eval() resolves without error update the numeric field.
Thanks

[CJ_London]

The thing I don't like about calculations done this way is that once calculated, the calculation is lost.

I have developed numerous stock take systems of the years. Nearest to this solution I use is to store the calculation as a (text) field in it's own right and the result in an unbound control for reporting although I guess you could store the calculated value as well.

doesn't matter if you just enter 20, 45+33, 8*48 or 2*16+3*24, the eval function will provide the solution subject to using the correct characters - I limit the available characters the user can type to 0-9, +, - and *.

I don't allow division or the use of brackets so maths priority of calculation is enforced i.e. multiply first, followed by addition/subtraction. Keeps it simple.

You could allow brackets, but users need to understand their use. So if you have 2 boxes of 20 over here and 4 boxes of 20 over there the user would type

2*20+4*20

and not

(2+4)*20

and quite honestly saves 1 character of typing - is it worth the risk of someone not not understanding how to use brackets?

[swas]

Thanks for the thoughts.
In my case keeping the calculation isn't a need or worry, rather a quick / better way than breaking out the calculator.

Or having to think.

Thanks
swas