Excel Macro Strings

[Brandon]

Hello, I am trying to use the concatenate operator to
combine 3 different individual strings with numbers in
them into one string. Something like this:

month = xx
day = xx
year = xx

date = month & "/" & day & "/" & year

But this doesn't work. Is this the correct operator to
use for this kind of purpose and I am just using it
incorrectly? Or is there a whole other way and I am
missing something?

 

[Dan E]

Brandon,

My first suggestion would be to use different variable names, month, day and
year are functions and date is a property. Try your code again with things
like myMonth, myDay, myYear and myDate, if it still acts up, post back

Dan E

 

[Brandon]

Yes, I did that already, the month/day/year was just for
showing what kind of number I am using to try and convert
into a date. Any other ideas?

 

[Dan E]

It looks correct to me then, what is the problem, does it output something
incorrect, nothing at all, etc . . . Perhaps a little more description would
help.

Dan E

 

[Brandon]

Just that there is an error. Program execution breaks on
that line and the debugger highlights in yellow with a
vague error message.

 

[Dan E]

Post the "vague error message"

Dan E

 

[Brandon]

Run-time error '13':
Type Mismatch


This is my code:

**********

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = DDERequest(Channel, "V30145:B")
PLCDay = DDERequest(Channel, "V30146:B")
PLCYear = DDERequest(Channel, "V30147:B")

DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

**********

When I retrieve the date values, they are all numbers,
not strings, and also-- the "/0" in the line that assigns
a value to PLCDate, the "0" is because when I retrieve
the year from the PLC (programmable logic controller), it
comes as 03 which becomes just '3'. I think it is just a
variable type inconsistency, like I am using numbers but
it is looking for a String.
Thanks for all the help you're giving, Dan E.

 

[Tom Ogilvy]

myday = 10
mymonth = 5
myyear = 2003
? mymonth & "/" & myday & "/" & myyear
5/10/2003
? cdate(mymonth & "/" & myday & "/" & myyear)
5/10/2003
? format(cdate(mymonth & "/" & myday & "/" & myyear),"mmm dd, yyyy")
May 10, 2003

What is "date" dimensioned as

Sub testdate()
Dim dtDate As Date
myday = 10
mymonth = 5
myyear = 2003
dtDate = mymonth & "/" & myday & "/" & myyear
Debug.Print dtDate
End Sub

worked.

You can try
msgbox isdate(mymonth & "/" & myday & "/" & myyear) & " " & _
mymonth & "/" & myday & "/" & myyear


to see if vba sees your string as a date

Regards,
Tom Ogilvy

 

[Dan E]

Brandon,

I won't guarantee anything, but you can give this a try, I can't test it out
because unfortunately I don't have any PLC's though their is an RTU or two
kicking around here. I put in some CStr (Convert to string) statements
which may be of use.

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = CStr(DDERequest(Channel, "V30145:B"))
PLCDay = CStr(DDERequest(Channel, "V30146:B"))
PLCYear = CStr(DDERequest(Channel, "V30147:B"))

DDETerminate Channel
'''''
PLCDate = CStr(PLCMonth & "/" & PLCDay & "/0" & PLCYear)
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

Dan E

 

[Tom Ogilvy]

Sub TMP_InstantLog()

Dim PLCMonth, PLCDay, PLCYear, PLCDate As String
'''''
'Channel = DDEInitiate("DSData", "TMP_DataRetrieve")

PLCMonth = 12 ' DDERequest(Channel, "V30145:B")
PLCDay = 5 'DDERequest(Channel, "V30146:B")
PLCYear = 3 'DDERequest(Channel, "V30147:B")

'DDETerminate Channel
'''''
PLCDate = PLCMonth & "/" & PLCDay & "/0" & PLCYear
curr_tmp = 3
Worksheets("Sheet1").Cells(curr_tmp, 18).Value = DateValue(PLCDate)

End Sub



worked fine for me - there must be something screwed up in your data.


try
msg = "->" & PLCMonth & "<-" & typename(PLCMonth) & vbNewline & _
"-> & PLCDay & "<-" & typename(PLCDay) & vbNewline & _
"-> & PLCYear & "<-" & typename(PLCYear)

msgbox msg
before PCLDate = . . .

 

[jaf]

Force it into a string.
Dim PLCMonth As String, PLCDay As String, PLCYear As String, PLCDate As
String

Worksheets("Sheet1").Cells(curr_TMP, 18).Value = CDate(PLCDate)

 

[Brandon]

Okay, I think what you asked me to do is what helped
address the problem the best. First, the code...

BEGIN PROGRAM
***

Sub TMP_InstantLog()

Dim temp1, temp2, temp3
Dim PLCMonth, PLCDay, PLCYear, PLCDate
Dim curr_TMP
curr_TMP = 4

Channel = DDEInitiate("DSData", "TMP_DataRetrieve")
temp1 = CInt(DDERequest(Channel, "V30145:B"))
temp2 = CInt(DDERequest(Channel, "V30146:B"))
temp3 = CInt(DDERequest(Channel, "V30147:B"))
DDETerminate Channel

PLCMonth = CStr(temp1)
PLCDay = CStr(temp2)
PLCYear = CStr(temp3)

PLCDate = PLCMonth & "/" & PLCDay) & "/0" & PLCYear)
Worksheets("Sheet1").Cells(curr_TMP, 18).Value = DateValue
(PLCDate)

***
END PROGRAM

The Error code 13 comes into play on the first of the PLC
variable assignment lines: PLCMonth = CStr(temp1)
It gets the value all right from the PLC, but for some
reason, it cannot convert them to String. I checked in my
DSData manual and it says nothing about the data type
they are forced into when they come into an Excel
spreadsheet, but for some reason- whatever it is- it
cannot be converted to a String right away. Does anyone
have any more ideas?

 

[Tom Ogilvy]

I gave you a suggestion that identified the problem (you have since
confirmed it) and suggested a way you might discover what type of data it
is, but you appear to be blowing it off.

Regards,
Tom Ogilvy

 

[jaf]

Hi Brandon,
Find out what is comming through the port first. Then figure out how to
format or convert it.

Sub TMP_InstantLog()

Dim temp1, temp2, temp3
Dim PLCMonth, PLCDay, PLCYear, PLCDate
Dim curr_TMP
curr_TMP = 4

Channel = DDEInitiate("DSData", "TMP_DataRetrieve")
temp1 = DDERequest(Channel, "V30145:B") '<<<<<<<<<<
temp2 = DDERequest(Channel, "V30146:B") '<<<<<<<<<<
temp3 = DDERequest(Channel, "V30147:B") '<<<<<<<<<<
DDETerminate Channel

Debug.Print temp1, temp2, temp3 '<<<<<<<<<<
stop '<<<<<<<<<<


Is it a byte, hex, decimal, text? Post back.

 

[Dan E]

Brandon,

You could give VarType a try

Sub TMP_InstantLog()

Dim temp1, temp2, temp3
Dim PLCMonth, PLCDay, PLCYear, PLCDate
Dim curr_TMP
curr_TMP = 4

Channel = DDEInitiate("DSData", "TMP_DataRetrieve")
temp1 = CInt(DDERequest(Channel, "V30145:B"))
temp2 = CInt(DDERequest(Channel, "V30146:B"))
temp3 = CInt(DDERequest(Channel, "V30147:B"))
DDETerminate Channel

MType = VarType(temp1)
DType = VarType(temp2)
YType = VarType(temp3)

Debug.Print MType, DType, YType

vbEmpty 0 Empty (uninitialized)
vbNull 1 Null (no valid data)
vbInteger 2 Integer
vbLong 3 Long integer
vbSingle 4 Single-precision floating-point number
vbDouble 5 Double-precision floating-point number
vbCurrency 6 Currency value
vbDate 7 Date value
vbString 8 String
vbObject 9 Object
vbError 10 Error value
vbBoolean 11 Boolean value
vbVariant 12 Variant (used only with arrays of variants)
vbDataObject 13 A data access object
vbDecimal 14 Decimal value
vbByte 17 Byte value
vbUserDefinedType 36 Variants that contain user-defined types
vbArray 8192 Array

 

[Tom Ogilvy]

Why not just use typename as I suggested and get the type in English.

Regards,
Tom Ogilvy

 

[Tom Ogilvy]

what about this: in my 2nd post:

msg = "->" & PLCMonth & "<-" & typename(PLCMonth) & vbNewline & _
"-> & PLCDay & "<-" & typename(PLCDay) & vbNewline & _
"-> & PLCYear & "<-" & typename(PLCYear)

msgbox msg
before PCLDate = . . .

That tells you what the type of the data is.

Regards,
Tom Ogilvy