I’m really enjoying the WinHTTP and Web Services (specifically REST) stuff. Even though I need to research quite a bit for each small thing I learn, and I’m just getting started, it’s well worth it.

Last night I figured out how to round trip a variable from VBA to PHP and back. In other words, I learned how to pass a VBA variable to a PHP function (residing in a file on the AutomateExcel server), manipulate the variable, then return the new result back to VBA.

Note: The variable is sent as a string.

For example, the message box in this picture was arrived at by:
1. Sending a variable holding the number 15 over HTTP to a PHP file on AutomateExcel
2. A PHP function takes the variable, Multiplies it by 500, then returns the answer, in this case 7500
3. VBA reads the response and returns the answer in a msgbox

Here’s what the PHP code looks like. Open notepad, copy and paste this info, save the file with the extension php, and upload to your server:

<?php 

$GetNumber=$_GET["PassThis"];
$NewNumber=$GetNumber*500; 

echo $GetNumber. " x 500 = " .$NewNumber; 

?>

I didn’t return the answer in XML to keep the example simple. Here’s the VBA code to send and receive the data, change the connection string to point to the php file you just created:

' Add a reference to Microsoft WinHTTP Services
Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0

Private Sub GetItems()
Dim MyCon As New WinHttpRequest
Dim sendthis As Double
Dim myanswer As String

    'variable to send
    sendthis = 15

    'Connection string to send
    MyCon.Open "GET", _
    "http://www.automateexcel.com/pl/server500.php" & _
    "?PassThis=" & sendthis

    'send it
    MyCon.Send

    'return it
    myanswer = MyCon.ResponseText
    MsgBox myanswer

End Sub

Note: Add a reference to Microsoft WinHTTP Services

That’s awesome!