I am using the code at http://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
"QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?

Here is the test code with the troublesome code highlighted. The
Excel file must have a spreadsheet with tab name "Test".

Option Explicit

Public Sub Aggregate()

Dim TargetString, OtherString As String
Dim SomeCell As Range
Dim ColorIndexRed As Integer

ColorIndexRed = 3
Set SomeCell = Sheets("Test").Range("A1")
TargetString = "QuickBrownFox"

' Troublesome code
'-------------------
'Call xlCellTextMgmt(SomeCell, _
' TargetString, , , , , ColorIndexRed)

Call xlCellTextMgmt(SomeCell, _
"QuickBrownFox", , , , , ColorIndexRed)

OtherString = "QuickBrownFox"
SomeCell = OtherString

Call xlCellTextMgmt(SomeCell, _
OtherString, , , , , ColorIndexRed)

End Sub

---------- Forwarded message ----------
From:
Date: Jun 3, 3:13*pm
Subject: "ByRef argument type mismtach" on string variable *sometimes*
To: microsoft.public.excel.programming


I am using the code athttp://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. *I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
* *TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. *However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
* *"QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
* *OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?

Here is the test code with the troublesome code highlighted. The
Excel file must have a spreadsheet with tab name "Test".

Option Explicit

Public Sub Aggregate()

Dim TargetString, OtherString As String
Dim SomeCell As Range
Dim ColorIndexRed As Integer

ColorIndexRed = 3
Set SomeCell = Sheets("Test").Range("A1")
TargetString = "QuickBrownFox"

' Troublesome code
'-------------------
'Call xlCellTextMgmt(SomeCell, _
' TargetString, , , , , ColorIndexRed)

Call xlCellTextMgmt(SomeCell, _
"QuickBrownFox", , , , , ColorIndexRed)

OtherString = "QuickBrownFox"
SomeCell = OtherString

Call xlCellTextMgmt(SomeCell, _
OtherString, , , , , ColorIndexRed)

End Sub

---------- Forwarded message ----------
From:
Date: Jun 3, 3:13*pm
Subject: "ByRef argument type mismtach" on string variable *sometimes*
To: microsoft.public.excel.programming


I am using the code athttp://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. *I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
* *TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. *However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
* *"QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
* *OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?

TargetString is a Variant and the called function is expecting a String...

Dim TargetString as String
Dim OtherString as String
--
Jim Cone
Portland, Oregon USA

TargetString is a Variant and the called function is expecting a String...

Dim TargetString as String
Dim OtherString as String
--
Jim Cone
Portland, Oregon USA

You need to declare TargetString as String, so:

Dim TargetString As String
Dim OtherString As String

When you do:
Dim TargetString, OtherString As String

Then TargetString is declared as a Variant.

The other options are to pass TargetString ByVal or do Cstr(TargetString)

RBS


wrote in message
...
I am using the code at http://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
"QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?

You need to declare TargetString as String, so:

Dim TargetString As String
Dim OtherString As String

When you do:
Dim TargetString, OtherString As String

Then TargetString is declared as a Variant.

The other options are to pass TargetString ByVal or do Cstr(TargetString)

RBS


wrote in message
...
I am using the code at http://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
"QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?

On Jun 3, 3:34*pm, "Jim Cone" wrote:
TargetString is a Variant and the called function is expecting a String....

Dim TargetString as String
Dim OtherString as String

Thanks, Jim. I need to read the Help more carefully (though
admittedly, this behaviour does depart from many other languages).

On Jun 3, 3:34*pm, "Jim Cone" wrote:
TargetString is a Variant and the called function is expecting a String....

Dim TargetString as String
Dim OtherString as String

Thanks, Jim. I need to read the Help more carefully (though
admittedly, this behaviour does depart from many other languages).

Thank you for that, RB.

On Jun 3, 3:40*pm, "RB Smissaert"
wrote:
You need to declare TargetString as String, so:

Dim TargetString As String
Dim OtherString As String

When you do:
Dim TargetString, OtherString As String

Then TargetString is declared as a Variant.

The other options are to pass TargetString ByVal or do Cstr(TargetString)

RBS

wrote in message

...



I am using the code athttp://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. *I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
* TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. *However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
* "QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
* OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?- Hide quoted text -

- Show quoted text -

Thank you for that, RB.

On Jun 3, 3:40*pm, "RB Smissaert"
wrote:
You need to declare TargetString as String, so:

Dim TargetString As String
Dim OtherString As String

When you do:
Dim TargetString, OtherString As String

Then TargetString is declared as a Variant.

The other options are to pass TargetString ByVal or do Cstr(TargetString)

RBS

wrote in message

...



I am using the code athttp://www.vbaexpress.com/kb/archive.php/k-743.html
to change the color of some text in an Excel 2003 spreadsheet. *I am
using the following type of invocation:

Dim TargetString, OtherString As String
TargetString = "QuickBrownFox"
Call xlCellTextMgmt( SomeCell, _
* TargetString , , , , , ColorIndexRed )

This generates the error in the subject line. *However, this
invocation is fine:

Call xlCellTextMgmt( SomeCell, _
* "QuickBrownFox", , , , , ColorIndexRed )

This invocation is fine, too:

OtherString = "QuickBrownFox"
SomeCell = OtherString
Call xlCellTextMgmt( SomeCell, _
* OtherString, , , , , ColorIndexRed )

What could possibly cause the first invocation to give the subject
error?- Hide quoted text -

- Show quoted text -