Decimal problem

  • Thread starter Thread starter Gustaf
  • Start date Start date
G

Gustaf

In a VBA project I'm supporting, I ran into a line where a cell is assigned the value

"=RC[4] / (" + Format(100 - dblNewProdSaving) + " / 100)"

I get a run-time error on this line. However, if I manually change the cell from where dblNewProdSaving is taken, from 0,02 to 0.02, it runs. Why is this and what can I do about it?

Gustaf
 
syntax for format is wrong

"=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)"

but i suggest

with rangetarget
.formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")"
.NumberFormat ="0.00%"
end with

format the result generally overcomes issues with results that can often
unexpectedly occur when an interim calculation is formatted , eg rounding
issue
 
Patrick said:
syntax for format is wrong

"=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)"
Click to expand...

That's what I thought too, but I get the same error. By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?
but i suggest

with rangetarget
.formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")"
.NumberFormat ="0.00%"
end with
Click to expand...

Sorry to say, but this also produces the same error: "Application defined or object defined error" (duh!). Also tried putting NumberFormat first.

As an alternative, I tried changing the format of the cell from which I take dblNewProdSaving:

dblNewProdSaving = Format(ws.Cells(5, 4), "0.00%")

But this results in a "type mismatch" error. Ideally, I'd like to change it behind the scenes, to avoid confusing the user.

Gustaf
 
what's the value in dblNewProdSaving when you step through?

Gustaf said:
Patrick said:
syntax for format is wrong

"=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)"
Click to expand...

That's what I thought too, but I get the same error. By the way, am I
right that VBA always expects "." as decimal sign, regardless of my
regional settings?
but i suggest

with rangetarget
.formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")"
.NumberFormat ="0.00%"
end with
Click to expand...

Sorry to say, but this also produces the same error: "Application defined
or object defined error" (duh!). Also tried putting NumberFormat first.

As an alternative, I tried changing the format of the cell from which I
take dblNewProdSaving:

dblNewProdSaving = Format(ws.Cells(5, 4), "0.00%")

But this results in a "type mismatch" error. Ideally, I'd like to change
it behind the scenes, to avoid confusing the user.

Gustaf
Click to expand...
 
Patrick said:
what's the value in dblNewProdSaving when you step through?
Click to expand...

The value is 0,02 in the cell and still 0,02 when transfered to dblNewProdSaving. If I change the cell to 0.02 the value of dblNewProdSaving becomes 2 when I step through. Looks odd but probably makes sense, because the variable is a percentage.

Gustaf
 
that makes a pretty big difference!

so
"=RC[4] / (" & (100 - dblNewProdSaving ) & ")"

should be

"=RC[4] / (" & (1 - dblNewProdSaving ) & ")"
 
"By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?"
No. It's governed by regional settings.
Click to expand...

But the regional settings can be overridden in Excel.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Hi Chip,
Yes, I should have pointed that out also.
Tools>options>international "system separators"

John
 
Back
Top