{"id":3533,"date":"2020-03-23T14:49:22","date_gmt":"2020-03-23T05:49:22","guid":{"rendered":"http:\/\/kats-eye.net\/info\/?p=3533"},"modified":"2020-03-23T22:43:20","modified_gmt":"2020-03-23T13:43:20","slug":"excel-arduino","status":"publish","type":"post","link":"https:\/\/kats-eye.net\/info\/2020\/03\/23\/excel-arduino\/","title":{"rendered":"\u30a8\u30af\u30bb\u30ebVBA\u3067ARDUINO\u306b\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3059\u308b"},"content":{"rendered":"\n\n\n\n
 <\/td>\n <\/td>\n\u30a8\u30af\u30bb\u30ebVBA to ARDUINO<\/span><\/strong><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n

\u3000\u6b21\u306e\u8a18\u4e8b\u3092\u53c2\u8003\u306b\u3055\u305b\u3066\u9802\u304d\u3001\u30a8\u30af\u30bb\u30ebVBA\u304b\u3089ARDUINO\u3078\u306e\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3092\u884c\u3063\u3066\u307f\u307e\u3059\u3002WindowsAPI\u3092\u4f7f\u3063\u3066\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3092\u884c\u3044\u307e\u3059\u3002
\u3010Excel VBA\u3067RS-232C\u901a\u4fe1\u3011\u3000\u3000
\u3000https:\/\/qiita.com\/pbjpkas\/items\/f81947ce38941356ebe4

\u3000\u5148\u65e5\u3001ARDUINO\u3068CNC\u2010SHIELD \u3092\u7528\u3044\u305f\u30b9\u30c6\u30c3\u30d4\u30f3\u30b0\u30e2\u30fc\u30bf\u30fc\u99c6\u52d5\u306b\u3064\u3044\u3066\u66f8\u304d\u307e\u3057\u305f\u3002\u30d1\u30bd\u30b3\u30f3\u304b\u3089\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3067ARDUINO\u306bG\u30b3\u30fc\u30c9\u9001\u4fe1\u3057\u3001\u30e2\u30fc\u30bf\u30fc\u3092\u52d5\u304b\u3059\u3068\u3044\u3046\u3082\u306e\u3067\u3057\u305f\u3002\u9014\u4e2d\u7d4c\u8def\u3092\u8003\u616e\u3057\u306a\u3044\u4f4d\u7f6e\u6c7a\u3081\u306e\u5834\u5408\u3001\u30a8\u30af\u30bb\u30eb\u30b7\u30fc\u30c8\u306e\u30bb\u30eb\u306b\u5165\u529b\u3055\u308c\u305f\u5ea7\u6a19\u306b\u57fa\u3065\u304d \u201cG90G0X***.**Y***.**\u201d \u306e\u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u3067\u30b7\u30ea\u30a2\u30eb\u9001\u4fe1\u3059\u308b\u3053\u3068\u3067\u3001\u9023\u7d9a\u7684\u4f4d\u7f6e\u6c7a\u3081\u304c\u51fa\u6765\u305d\u3046\u3067\u3059\u3002\u5148\u305a\u30a8\u30af\u30bb\u30ebVBA\u304b\u3089\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3059\u308b\u65b9\u6cd5\u306b\u3064\u3044\u3066\u8abf\u3079\u307e\u3057\u305f\u3002
<\/p>\n\n\n\n\n\n\n
 <\/td>\n <\/td>\n\u30d7\u30ed\u30b0\u30e9\u30e0\u6982\u8981<\/span><\/strong><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n

\u3000\u52d5\u4f5c\u306f\u6b21\u306e\u56f3\u306e\u901a\u308a\u3001\u30a8\u30af\u30bb\u30eb\u30b7\u30fc\u30c8\u4e0a\u306e\u9001\u4fe1\u30c7\u30fc\u30bf\u6b04\u306b\u6587\u5b57\u3092\u5165\u529b\u3057\u3001\u53f3\u5074\u306e\u201c\u9001\u4fe1\u30dc\u30bf\u30f3\u201d\u3092\u30af\u30ea\u30c3\u30af\u3059\u308b\u3068USB\uff08RS232C\uff09\u3092\u4ecb\u3057\u3066 ARDUINO \u306b\u30b7\u30ea\u30a2\u30eb\u30c7\u30fc\u30bf\u9001\u4fe1\u3057\u307e\u3059\u3002ARDUINO\u306f\u53d7\u4fe1\u3057\u305f\u30c7\u30fc\u30bf\u306e\u5148\u982d\u306b [RECEIVED] \u3068\u3044\u3046\u6587\u5b57\u3092\u4ed8\u4e0e\u3057\u8fd4\u9001\u3057\u307e\u3059\u3002\u30a8\u30af\u30bb\u30eb\u5074\u3067\u306f\u53d7\u4fe1\u30c7\u30fc\u30bf\u3092\u30a8\u30af\u30bb\u30eb\u30b7\u30fc\u30c8\u306b\u8868\u793a\u3057\u307e\u3059\u3002\uff08\u8d64\u7dda\u67a0\u5185\uff09<\/p>\n\n\n\n

\"\"

<\/figcaption><\/figure>\n\n\n\n\n\n\n
 <\/td>\n <\/td>\n\u30a8\u30af\u30bb\u30ebVBA \u30d7\u30ed\u30b0\u30e9\u30e0<\/span><\/strong><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
\n

\u3000\u6b21\u306f\u30a8\u30af\u30bb\u30ebVBA\u306e\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3092\u884c\u3046\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u3059\u3002
\u3000Public Function StartSerialComm(str_TX As String, com_port As String)<\/strong>\u3000\u306b\u9001\u4fe1\u30c7\u30fc\u30bf\u3068\u30dd\u30fc\u30c8\u756a\u53f7\u3092\u6307\u5b9a\u3057\u3066\u3001\u547c\u3073\u51fa\u3059\u3068\u30c7\u30fc\u30bf\u3092\u9001\u4fe1\u3057\u3001\u8fd4\u4fe1\u30c7\u30fc\u30bf\u3092\u53d7\u4fe1\u3059\u308b\u3053\u3068\u304c\u51fa\u6765\u307e\u3059\u3002<\/p>\n<\/div><\/div>\n\n\n\n

Option Explicit\n\nPrivate Declare Function CreateFile Lib \"kernel32\" Alias \"CreateFileA\" _\n   (ByVal lpFileName As String, _\n    ByVal dwDesiredAccess As Long, _\n    ByVal dwShareMode As Long, _\n    ByVal lpSecurityAttributes As Long, _\n    ByVal dwCreationDisposition As Long, _\n    ByVal dwFlagsAndAttributes As Long, _\n    ByVal hTemplateFile As Long) As Long\n\nPrivate Declare Sub CloseHandle Lib \"kernel32\" (ByVal hObject As Long)\n\nPrivate Declare Sub ReadFile Lib \"kernel32\" _\n   (ByVal hFile As Long, _\n    ByVal lpBuffer As String, _\n    ByVal nNumberOfBytesToRead As Long, _\n          lpNumberOfBytesRead As Long, _\n    ByVal lpOverlapped As Long)\n\nPrivate Declare Sub WriteFile Lib \"kernel32\" _\n   (ByVal hFile As Long, _\n    ByVal lpBuffer As String, _\n    ByVal nNumberOfBytesToWrite As Long, _\n          lpNumberOfBytesWritten As Long, _\n    ByVal lpOverlapped As Long)\n\nPrivate Declare Function SetCommState Lib \"kernel32\" (ByVal hFile As Long, ByRef lpDCB As DCB) As Long\nPrivate Declare Sub SetCommTimeouts Lib \"kernel32\" (ByVal hFile As Long, lpCommTimeouts As COMMTIMEOUTS)\n\n'SetCommState\nPrivate Type DCB\n    DCBlength As Long\n    BaudRate As Long\n    Fields As Long\n    wReserved As Integer\n    XonLim As Integer\n    XoffLim As Integer\n    ByteSize As Byte\n    Parity As Byte\n    StopBits As Byte\n    XonChar As Byte\n    XoffChar As Byte\n    ErrorChar As Byte\n    EofChar As Byte\n    EvtChar As Byte\n    wReserved1 As Integer\nEnd Type\n\n'SetCommTimeouts\nPrivate Type COMMTIMEOUTS\n    ReadIntervalTimeout As Long\n    ReadTotalTimeoutMultiplier As Long\n    ReadTotalTimeoutConstant As Long\n    WriteTotalTimeoutMultiplier As Long\n    WriteTotalTimeoutConstant As Long\nEnd Type\n\n\n'CreateFile\u30d1\u30e9\u30e1\u30fc\u30bf\u7528\u5b9a\u6570\nPrivate Const GENERIC_READ = (&H80000000)\nPrivate Const GENERIC_WRITE = (&H40000000)\nPrivate Const OPEN_EXISTING = 3\n\n\nPublic Function StartSerialComm(str_TX As String, com_port As String)\n    Dim comPort As String\n    Dim hFile As Long\n    Dim cs As DCB          'CommState\n    Dim ct As COMMTIMEOUTS 'CommTimeouts\n    Dim length As Long\n    Dim buf As String\n\n    comPort = com_port\n\n    'COM\u30dd\u30fc\u30c8\u30aa\u30fc\u30d7\u30f3\n    hFile = CreateFile(comPort, GENERIC_READ Or GENERIC_WRITE, 0, 0, OPEN_EXISTING, 0, 0)\n    If hFile = -1 Then\n        StartSerialComm = \"ERR_COM_OPEN\"\n        Exit Function\n    End If\n\n    'RS232C\u901a\u4fe1\u8a2d\u5b9a\n    cs.BaudRate = 9600\n    cs.ByteSize = 8\n    cs.Parity = 0\n    cs.StopBits = 0\n    cs.Fields = &H3001\n    SetCommState hFile, cs\n\n    'RS232C\u30bf\u30a4\u30e0\u30a2\u30a6\u30c8\u8a2d\u5b9a\n    ct.ReadIntervalTimeout = 1000\n    ct.ReadTotalTimeoutMultiplier = 1\n    ct.ReadTotalTimeoutConstant = 500\n    ct.WriteTotalTimeoutMultiplier = 1\n    ct.WriteTotalTimeoutConstant = 500\n    SetCommTimeouts hFile, ct\n\n    '\u9001\u4fe1\n    buf = str_TX + Chr(10)\n    WriteFile hFile, buf, Len(buf), length, 0\n\n    '\u53d7\u4fe1\n    buf = String(1024, vbNullChar)\n    ReadFile hFile, buf, Len(buf), length, 0\n\n    'COM\u30dd\u30fc\u30c8\u30af\u30ed\u30fc\u30ba\n    CloseHandle hFile\n\n    '\u53d7\u4fe1\u30c7\u30fc\u30bf\u306e\u30d1\u30fc\u30b9\n    buf = Replace(buf, vbNullChar, \"\")\n    buf = Replace(buf, Chr(10), \"\")\n    buf = Replace(buf, Chr(13), \"\")\n    \n    StartSerialComm = buf\n\nEnd Function<\/code><\/pre>\n\n\n\n
\u3000\u547c\u3073\u51fa\u3057\u306f\u6b21\u306e\u69d8\u306a\u611f\u3058\u3067\u3059\u3002\u30b7\u30fc\u30c8\u306b\u8cbc\u308a\u4ed8\u3051\u305f\u30b3\u30de\u30f3\u30c9\u30dc\u30bf\u30f3\u306e\u30af\u30ea\u30c3\u30af\u30a4\u30d9\u30f3\u30c8\u3067\u5b9f\u884c\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
Option Explicit\n\nPrivate Sub CommandButton1_Click()\n    Dim sht\n    Dim res\n    \n    sht = ActiveSheet.Name\n    \n    Worksheets(sht).Cells(3, 3).Value = \"\"\n    \n    res = StartSerialComm(Trim(Worksheets(sht).Cells(2, 3).Value), \"COM5\")\n    \n    Worksheets(sht).Cells(3, 3).Value = res\n    \nEnd Sub<\/code><\/pre>\n\n\n\n

<\/p>\n\n\n\n\n\n\n
 <\/td>\n <\/td>\nARDUINO \u30d7\u30ed\u30b0\u30e9\u30e0<\/span><\/strong><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
\u3000ARDUINO \u5074\u306e\u30d7\u30ed\u30b0\u30e9\u30e0\u3067\u3059\u3002<\/p>\n\n\n\n
void setup() {\n  Serial.begin(9600);\n  Serial.setTimeout(15000UL);\n  Serial.println(\"Start!\");\n}\n\nvoid loop() {\n  \/\/ put your main code here, to run repeatedly:\n  if( Serial.available() > 0 ){\n\n    \/\/ \\r:CR, \\r\\n:CRLF, \\n:LF\n    String str = Serial.readStringUntil('\\n');\n    Serial.println((String)\"[RECEIVED] \" + str);\n  }\n}<\/code><\/pre>\n\n\n\n

<\/p>\n\n\n\n\n\n\n
 <\/td>\n <\/td>\n\u307e\u3068\u3081<\/span><\/strong><\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n
\u3000\u30d0\u30fc\u30b8\u30e7\u30f3\u30a2\u30c3\u30d7\u3057\u305fWordPress\u306b\u4e0d\u6163\u308c\u3067\u75b2\u308c\u307e\u3057\u305f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
    \u30a8\u30af\u30bb\u30ebVBA to ARDUINO \u3000\u6b21\u306e\u8a18\u4e8b\u3092\u53c2\u8003\u306b\u3055\u305b\u3066\u9802\u304d\u3001\u30a8\u30af\u30bb\u30ebVBA\u304b\u3089ARDUINO\u3078\u306e\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3092\u884c\u3063\u3066\u307f\u307e\u3059\u3002WindowsAPI\u3092\u4f7f\u3063\u3066\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3092\u884c\u3044\u307e\u3059\u3002\u3010Exc … \u7d9a\u304d\u3092\u8aad\u3080 \u30a8\u30af\u30bb\u30ebVBA\u3067ARDUINO\u306b\u30b7\u30ea\u30a2\u30eb\u901a\u4fe1\u3059\u308b<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":3538,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[5,98],"tags":[9,99,100],"_links":{"self":[{"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/posts\/3533"}],"collection":[{"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/comments?post=3533"}],"version-history":[{"count":25,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/posts\/3533\/revisions"}],"predecessor-version":[{"id":3587,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/posts\/3533\/revisions\/3587"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/media\/3538"}],"wp:attachment":[{"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/media?parent=3533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/categories?post=3533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kats-eye.net\/info\/wp-json\/wp\/v2\/tags?post=3533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}