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


"Gustaf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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 wrote:

> 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

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

"Gustaf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Patrick Molloy wrote:
>
>> 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 wrote:

> 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

that makes a pretty big difference!

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

should be

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


"Gustaf" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Patrick Molloy wrote:
>
>> 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

"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

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

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)


On Wed, 3 Jun 2009 12:09:39 -0400, "jaf" <(E-Mail Removed)> wrote:

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

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

John


"Chip Pearson" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> >"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.
>
> 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)
>
>
> On Wed, 3 Jun 2009 12:09:39 -0400, "jaf" <(E-Mail Removed)> wrote:
>
>>"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