Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
Just that there is an error. Program execution breaks on
that line and the debugger highlights in yellow with a vague error message. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
Post the "vague error message"
Dan E "Brandon" wrote in message ... Just that there is an error. Program execution breaks on that line and the debugger highlights in yellow with a vague error message. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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" wrote in message ... Just that there is an error. Program execution breaks on that line and the debugger highlights in yellow with a vague error message. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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 "Brandon" wrote in 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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 = . . . -- Regards, Tom Ogilvy "Brandon" wrote in 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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) -- John johnf202 at hotmail dot com "Brandon" wrote in 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
Why not just use typename as I suggested and get the type in English.
Regards, Tom Ogilvy Dan E wrote in message ... 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 "Brandon" wrote in message ... Sorry, I didn't really understand your post. I saw the "what data type is date" but then you tested something and said it worked so I thought you found out yourself. Is there another way to test other than just "isdate", because that is the only one I found in VB Excel help. In my last post I showed the problem lies in the format of the number I am retrieving from an outside source. It is not a date, I tested the IsDate(temp1), but I can't test if it is an Int with IsInt, or if it's a string with IsString. Is there any other way I can find out what data type the number is stored as? And even if i do somehow find out, how can something just not be converted to a String if it is some form of number? I have never seen anything like this error in any programming I have done before. Thanks for any help you can still give me Tom. -Brandon |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro Strings
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 Brandon wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
Parsing Name Strings In Excel | Excel Discussion (Misc queries) | |||
Macro to apply parsing rules for strings and list the substrings | Excel Worksheet Functions | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions | |||
How can I count strings within strings | Excel Worksheet Functions |