Excel Macro Strings

  • Thread starter Thread starter Brandon
  • Start date Start date
B

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?
 
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
 
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?
 
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
 
Just that there is an error. Program execution breaks on
that line and the debugger highlights in yellow with a
vague error message.
 
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.
 
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
 
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
 
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 = . . .
 
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)
 
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?
 
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
 
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.
 
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
 
Why not just use typename as I suggested and get the type in English.

Regards,
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
 
Back
Top