Decimal problem

[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

 

[Patrick Molloy]

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

 

[Gustaf]

syntax for format is wrong

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

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

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

 

[Patrick Molloy]

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

syntax for format is wrong

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

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

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

 

[Gustaf]

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

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

 

[Patrick Molloy]

that makes a pretty big difference!

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

should be

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

 

[jaf]

"By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?"

Gustaf

No. It's governed by regional settings.

John

 

[Chip Pearson]

"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.

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)

 

[jaf]

Hi Chip,
Yes, I should have pointed that out also.
Tools>options>international "system separators"

John