What in the world is it doing??

Have run into a crazy problem. Have a macro that scans selected cells and,
if the content is a string, does a string manipulation. However, have
discovered that if the content is a certain type of string, VBA goes into
orbit.

Essence of situation: Select a series of cells in a pivot table. Cells
format is General. Execute a loop: For each cell in selection, if
typename(cell.value) = "String" then cell.value = "0" & cell.value.

Typical string data is <533: Then Text>. Code works fine to make this
<0533: Then Text>. However, when data is <715: 1.2>, cell.value tests true
as string, but cell.value = "0" & cell.value causes a cell value of
<0.496541666666667> instead of the intended <0715: 1.2>, is still format
General, typename string, and the worksheet is wacked out and VBA code jumps
out of the sub and starts executing in other subs that aren't even
referenced in it. End up having to use Task Manager to kill Excel, then
start over. So, what in the world is going on??

Win XP Pro, Office XP, both with all updates.

Thanks in advance for any ideas!
George

Sounds like deja-vu all over again to me. The effect you describe happened to
me a long time ago and I believe it was just the result of "dirty code
modules". I attributed it to having written and revised too much code without
using Rob Bovey's CodeCleaner. If I recollect correctly, when I finally did
use CodeCleaner, I couldn't open the wb again because it got corrupted. I
won't blame it on CodeCleaner but I think, if I were you, I'd save the
modules individually manually before using CodeCleaner. I would be extremely
careful at this point.

Then of course, it could be something to do with the complexities of Pivot
Tables and not what I describe.

Regards,
Greg

"G Lykos" wrote:

> Have run into a crazy problem. Have a macro that scans selected cells and,
> if the content is a string, does a string manipulation. However, have
> discovered that if the content is a certain type of string, VBA goes into
> orbit.
>
> Essence of situation: Select a series of cells in a pivot table. Cells
> format is General. Execute a loop: For each cell in selection, if
> typename(cell.value) = "String" then cell.value = "0" & cell.value.
>
> Typical string data is <533: Then Text>. Code works fine to make this
> <0533: Then Text>. However, when data is <715: 1.2>, cell.value tests true
> as string, but cell.value = "0" & cell.value causes a cell value of
> <0.496541666666667> instead of the intended <0715: 1.2>, is still format
> General, typename string, and the worksheet is wacked out and VBA code jumps
> out of the sub and starts executing in other subs that aren't even
> referenced in it. End up having to use Task Manager to kill Excel, then
> start over. So, what in the world is going on??
>
> Win XP Pro, Office XP, both with all updates.
>
> Thanks in advance for any ideas!
> George
>
>
>

Hi George,

What it's doing is converting the cell contents (715: 1.2) to a time value:
11:55:01 AM, actually. Where it heads off to from there I've no idea.

You can prevent the conversion by formatting the target cell as text:

With oCell
.NumberFormat = "@"
.Value = "0" & .Value
End With

Cheers
--
macropod
[MVP - Microsoft Word]


"G Lykos" <[email protected]> wrote in message
news:%[email protected]...
> Have run into a crazy problem. Have a macro that scans selected cells
and,
> if the content is a string, does a string manipulation. However, have
> discovered that if the content is a certain type of string, VBA goes into
> orbit.
>
> Essence of situation: Select a series of cells in a pivot table. Cells
> format is General. Execute a loop: For each cell in selection, if
> typename(cell.value) = "String" then cell.value = "0" & cell.value.
>
> Typical string data is <533: Then Text>. Code works fine to make this
> <0533: Then Text>. However, when data is <715: 1.2>, cell.value tests
true
> as string, but cell.value = "0" & cell.value causes a cell value of
> <0.496541666666667> instead of the intended <0715: 1.2>, is still format
> General, typename string, and the worksheet is wacked out and VBA code
jumps
> out of the sub and starts executing in other subs that aren't even
> referenced in it. End up having to use Task Manager to kill Excel, then
> start over. So, what in the world is going on??
>
> Win XP Pro, Office XP, both with all updates.
>
> Thanks in advance for any ideas!
> George
>
>

Thanks, Greg. Reminds me that I saw mention somewhere online about the Code
Cleaner that also highly recommended it - must be very good, will give it a
shot.

Regards,
George


"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> Sounds like deja-vu all over again to me. The effect you describe happened
to
> me a long time ago and I believe it was just the result of "dirty code
> modules". I attributed it to having written and revised too much code
without
> using Rob Bovey's CodeCleaner. If I recollect correctly, when I finally
did
> use CodeCleaner, I couldn't open the wb again because it got corrupted. I
> won't blame it on CodeCleaner but I think, if I were you, I'd save the
> modules individually manually before using CodeCleaner. I would be
extremely
> careful at this point.
>
> Then of course, it could be something to do with the complexities of Pivot
> Tables and not what I describe.
>
> Regards,
> Greg
>
> "G Lykos" wrote:
>
> > Have run into a crazy problem. Have a macro that scans selected cells
and,
> > if the content is a string, does a string manipulation. However, have
> > discovered that if the content is a certain type of string, VBA goes
into
> > orbit.
> >
> > Essence of situation: Select a series of cells in a pivot table. Cells
> > format is General. Execute a loop: For each cell in selection, if
> > typename(cell.value) = "String" then cell.value = "0" & cell.value.
> >
> > Typical string data is <533: Then Text>. Code works fine to make this
> > <0533: Then Text>. However, when data is <715: 1.2>, cell.value tests
true
> > as string, but cell.value = "0" & cell.value causes a cell value of
> > <0.496541666666667> instead of the intended <0715: 1.2>, is still format
> > General, typename string, and the worksheet is wacked out and VBA code
jumps
> > out of the sub and starts executing in other subs that aren't even
> > referenced in it. End up having to use Task Manager to kill Excel, then
> > start over. So, what in the world is going on??
> >
> > Win XP Pro, Office XP, both with all updates.
> >
> > Thanks in advance for any ideas!
> > George
> >
> >
> >

Thanks for the suggestion - setting NumberFormat to "@" allows the value to
be set to the desired string value. Kind of nutty is that Excel converts
the string <715: 1.2> to time; turns out that it doesn't care how many
blanks are in the middle. Perhaps it just spots the colon and calls it a
day.

A follow-on question is: I can't find any information about NumberFormat.
It appears that the "@" serves to position string data in Value
before/after, so it's not shorthand for string type but more like an
operator. I fooled with it a little to see if I could set the NumberFormat
back to General after setting the string value, but Excel wouldn't accept
NumberFormat = "General". I also tried to set the cell format to Time in
foreground just to see what it looked like after the undesired conversion
but Excel refused. Seems that the string value "715: 1.2" really craps up
something inside, or else I badly misunderstand whatever it does. Anyhow,
guidance on where to find information on NumberFormat would be appreciated
if any sources come to mind.

Thanks,
George


"macropod" <[email protected]> wrote in message
news:[email protected]...
> Hi George,
>
> What it's doing is converting the cell contents (715: 1.2) to a time
value:
> 11:55:01 AM, actually. Where it heads off to from there I've no idea.
>
> You can prevent the conversion by formatting the target cell as text:
>
> With oCell
> .NumberFormat = "@"
> .Value = "0" & .Value
> End With
>
> Cheers
> --
> macropod
> [MVP - Microsoft Word]
>
>
> "G Lykos" <[email protected]> wrote in message
> news:%[email protected]...
> > Have run into a crazy problem. Have a macro that scans selected cells
> and,
> > if the content is a string, does a string manipulation. However, have
> > discovered that if the content is a certain type of string, VBA goes
into
> > orbit.
> >
> > Essence of situation: Select a series of cells in a pivot table. Cells
> > format is General. Execute a loop: For each cell in selection, if
> > typename(cell.value) = "String" then cell.value = "0" & cell.value.
> >
> > Typical string data is <533: Then Text>. Code works fine to make this
> > <0533: Then Text>. However, when data is <715: 1.2>, cell.value tests
> true
> > as string, but cell.value = "0" & cell.value causes a cell value of
> > <0.496541666666667> instead of the intended <0715: 1.2>, is still format
> > General, typename string, and the worksheet is wacked out and VBA code
> jumps
> > out of the sub and starts executing in other subs that aren't even
> > referenced in it. End up having to use Task Manager to kill Excel, then
> > start over. So, what in the world is going on??
> >
> > Win XP Pro, Office XP, both with all updates.
> >
> > Thanks in advance for any ideas!
> > George
> >
> >
>
>

George,

Caution: I don't know if you read my post closely, but I had the same thing
happen to me where macros in other modules not even related to the macro
being run fired and the worksheet froze. This was a long time ago and I don't
remember exactly; but I believe this one corrupted immediately after using
CodeCleaner and I could never open it again - i.e. a total write-off !!!.
Obviously it was corrupted before running CodeCleaner but at least I could
open it. My advise is NOT to use CodeCleaner at this point just in case, but
to first save your code manually and also ensure you have a copy of the wb
before doing anything.

Greg

"G Lykos" wrote:

> Thanks, Greg. Reminds me that I saw mention somewhere online about the Code
> Cleaner that also highly recommended it - must be very good, will give it a
> shot.
>
> Regards,
> George
>
>
> "Greg Wilson" <[email protected]> wrote in message
> news:[email protected]...
> > Sounds like deja-vu all over again to me. The effect you describe happened
> to
> > me a long time ago and I believe it was just the result of "dirty code
> > modules". I attributed it to having written and revised too much code
> without
> > using Rob Bovey's CodeCleaner. If I recollect correctly, when I finally
> did
> > use CodeCleaner, I couldn't open the wb again because it got corrupted. I
> > won't blame it on CodeCleaner but I think, if I were you, I'd save the
> > modules individually manually before using CodeCleaner. I would be
> extremely
> > careful at this point.
> >
> > Then of course, it could be something to do with the complexities of Pivot
> > Tables and not what I describe.
> >
> > Regards,
> > Greg
> >
> > "G Lykos" wrote:
> >
> > > Have run into a crazy problem. Have a macro that scans selected cells
> and,
> > > if the content is a string, does a string manipulation. However, have
> > > discovered that if the content is a certain type of string, VBA goes
> into
> > > orbit.
> > >
> > > Essence of situation: Select a series of cells in a pivot table. Cells
> > > format is General. Execute a loop: For each cell in selection, if
> > > typename(cell.value) = "String" then cell.value = "0" & cell.value.
> > >
> > > Typical string data is <533: Then Text>. Code works fine to make this
> > > <0533: Then Text>. However, when data is <715: 1.2>, cell.value tests
> true
> > > as string, but cell.value = "0" & cell.value causes a cell value of
> > > <0.496541666666667> instead of the intended <0715: 1.2>, is still format
> > > General, typename string, and the worksheet is wacked out and VBA code
> jumps
> > > out of the sub and starts executing in other subs that aren't even
> > > referenced in it. End up having to use Task Manager to kill Excel, then
> > > start over. So, what in the world is going on??
> > >
> > > Win XP Pro, Office XP, both with all updates.
> > >
> > > Thanks in advance for any ideas!
> > > George
> > >
> > >
> > >
>
>
>

Yes, I got the message, but thanks for the additional caution.

Another one of the "interesting" behaviors is jumping to a sub which
consists simply of a Select Case ladder to categorize an entering value and
then return. Stepping through the code, it falls through the ladder, finds
a true rung, executes the line inside the rung, falls to the End, then
immediately jumps back up to the Select Case and starts down the ladder
again. It appears that _cell.value =_ operating on a string like _172:
15.3_ really knocks Excel cross-eyed. Yee-hah.


"Greg Wilson" <[email protected]> wrote in message
news:[email protected]...
> George,
>
> Caution: I don't know if you read my post closely, but I had the same
thing
> happen to me where macros in other modules not even related to the macro
> being run fired and the worksheet froze. This was a long time ago and I
don't
> remember exactly; but I believe this one corrupted immediately after using
> CodeCleaner and I could never open it again - i.e. a total write-off !!!.
> Obviously it was corrupted before running CodeCleaner but at least I could
> open it. My advise is NOT to use CodeCleaner at this point just in case,
but
> to first save your code manually and also ensure you have a copy of the wb
> before doing anything.
>
> Greg
>
> "G Lykos" wrote:
>
> > Thanks, Greg. Reminds me that I saw mention somewhere online about the
Code
> > Cleaner that also highly recommended it - must be very good, will give
it a
> > shot.
> >
> > Regards,
> > George
> >
> >
> > "Greg Wilson" <[email protected]> wrote in message
> > news:[email protected]...
> > > Sounds like deja-vu all over again to me. The effect you describe
happened
> > to
> > > me a long time ago and I believe it was just the result of "dirty code
> > > modules". I attributed it to having written and revised too much code
> > without
> > > using Rob Bovey's CodeCleaner. If I recollect correctly, when I
finally
> > did
> > > use CodeCleaner, I couldn't open the wb again because it got
corrupted. I
> > > won't blame it on CodeCleaner but I think, if I were you, I'd save the
> > > modules individually manually before using CodeCleaner. I would be
> > extremely
> > > careful at this point.
> > >
> > > Then of course, it could be something to do with the complexities of
Pivot
> > > Tables and not what I describe.
> > >
> > > Regards,
> > > Greg
> > >
> > > "G Lykos" wrote:
> > >
> > > > Have run into a crazy problem. Have a macro that scans selected
cells
> > and,
> > > > if the content is a string, does a string manipulation. However,
have
> > > > discovered that if the content is a certain type of string, VBA goes
> > into
> > > > orbit.
> > > >
> > > > Essence of situation: Select a series of cells in a pivot table.
Cells
> > > > format is General. Execute a loop: For each cell in selection, if
> > > > typename(cell.value) = "String" then cell.value = "0" & cell.value.
> > > >
> > > > Typical string data is <533: Then Text>. Code works fine to make
this
> > > > <0533: Then Text>. However, when data is <715: 1.2>, cell.value
tests
> > true
> > > > as string, but cell.value = "0" & cell.value causes a cell value of
> > > > <0.496541666666667> instead of the intended <0715: 1.2>, is still
format
> > > > General, typename string, and the worksheet is wacked out and VBA
code
> > jumps
> > > > out of the sub and starts executing in other subs that aren't even
> > > > referenced in it. End up having to use Task Manager to kill Excel,
then
> > > > start over. So, what in the world is going on??
> > > >
> > > > Win XP Pro, Office XP, both with all updates.
> > > >
> > > > Thanks in advance for any ideas!
> > > > George
> > > >
> > > >
> > > >
> >
> >
> >

Hi George,

To get back to 'General' you'd use:

With oCell
.NumberFormat = "@"
.Value = "0" & .Value
.NumberFormat = ""
End With

In other words, 'General' = nothing.

I'm not aware of any particular resources on Excel number formats, other
than what you can see via Format|Cells|Number, the examples in the Excel
help files, and the snippets you can find doing web searches for 'Excel vba
NumberFormat'.

Cheers

--
macropod
[MVP - Microsoft Word]


"G Lykos" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the suggestion - setting NumberFormat to "@" allows the value
to
> be set to the desired string value. Kind of nutty is that Excel converts
> the string <715: 1.2> to time; turns out that it doesn't care how many
> blanks are in the middle. Perhaps it just spots the colon and calls it a
> day.
>
> A follow-on question is: I can't find any information about NumberFormat.
> It appears that the "@" serves to position string data in Value
> before/after, so it's not shorthand for string type but more like an
> operator. I fooled with it a little to see if I could set the
NumberFormat
> back to General after setting the string value, but Excel wouldn't accept
> NumberFormat = "General". I also tried to set the cell format to Time in
> foreground just to see what it looked like after the undesired conversion
> but Excel refused. Seems that the string value "715: 1.2" really craps up
> something inside, or else I badly misunderstand whatever it does. Anyhow,
> guidance on where to find information on NumberFormat would be appreciated
> if any sources come to mind.
>
> Thanks,
> George
>
>
> "macropod" <[email protected]> wrote in message
> news:[email protected]...
> > Hi George,
> >
> > What it's doing is converting the cell contents (715: 1.2) to a time
> value:
> > 11:55:01 AM, actually. Where it heads off to from there I've no idea.
> >
> > You can prevent the conversion by formatting the target cell as text:
> >
> > With oCell
> > .NumberFormat = "@"
> > .Value = "0" & .Value
> > End With
> >
> > Cheers
> > --
> > macropod
> > [MVP - Microsoft Word]
> >
> >
> > "G Lykos" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Have run into a crazy problem. Have a macro that scans selected cells
> > and,
> > > if the content is a string, does a string manipulation. However, have
> > > discovered that if the content is a certain type of string, VBA goes
> into
> > > orbit.
> > >
> > > Essence of situation: Select a series of cells in a pivot table.
Cells
> > > format is General. Execute a loop: For each cell in selection, if
> > > typename(cell.value) = "String" then cell.value = "0" & cell.value.
> > >
> > > Typical string data is <533: Then Text>. Code works fine to make this
> > > <0533: Then Text>. However, when data is <715: 1.2>, cell.value tests
> > true
> > > as string, but cell.value = "0" & cell.value causes a cell value of
> > > <0.496541666666667> instead of the intended <0715: 1.2>, is still
format
> > > General, typename string, and the worksheet is wacked out and VBA code
> > jumps
> > > out of the sub and starts executing in other subs that aren't even
> > > referenced in it. End up having to use Task Manager to kill Excel,
then
> > > start over. So, what in the world is going on??
> > >
> > > Win XP Pro, Office XP, both with all updates.
> > >
> > > Thanks in advance for any ideas!
> > > George
> > >
> > >
> >
> >
>
>