Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

Note: I am limited to PHP <-> VBA. Please do not suggest anything that requires an Excel Addon, or any other language/method.

I have a function that connect to a specified URL, submits data, and then retrieves other data. This works great. I'm trying to write it so i can use it as a generic function I can use to connect to any file I need to connect to - each would return different data (one could be user data, one could be complex calculations etc).

When it retrieves the data from PHP, is there a way to dynamically set the variables based on what is received - even if i do not know what has been received.

I can make PHP return to VBA the string in any format, so I'm using the below as an example:

String that is received in vba:

myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday

If i were to parse this in PHP, I could do something similar to (not accurate, just written for example purposes);

$myData = "myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday"
$myArr = explode("&",$myData)
foreach($myArr as $key => $value){
    ${$key} = $value;
}
echo $someOtherValue; //Would output to the screen 'Hockey';

I would like to do something similar in VBA. The string I am receiving is from a PHP file, so I can format it any way (json etc etc), I just essentially want to be able to define the VARIABLES when outputting the string from PHP. Is this possible in VBA?.

The current state of the function I have that is working great for connections is as below:-

Function kick_connect(url As String, formdata)

'On Error GoTo connectError
Dim http
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "POST", url, False
http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

http.send (formdata)
kick_connect = http.responseText
Exit Function

connectError:
kick_connect = False

End Function

Ultimately, I want to be able to do something like

sub mySub
    myData = "getId=" & Range("A1").Value
    myValue = kick_connect("http://path-to-my-php-file.php",myData)
    if myValue = False then
        'Handle connection error here
        exit sub
    end if

    'do something snazzy here to split "myValue" string (eg "myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday") into own variables

    msgbox(myValue1) 'Should output "Dave"


end sub

Obviously I could put the values into an array, and reference that, however I specifically want to know if this exact thing is possible, to allow for flexibility with the scripts that already exist.

I hope this makes sense, and am really grateful for any replies i get.

Thank you.

share|improve this question
    
No, you cannot generate dynamic symbols on the fly. Use a collection/dictionary - foo("myValue1") or return XML and wrap DOM access in a class. –  Alex K. Apr 24 at 10:24

2 Answers 2

up vote 2 down vote accepted

You can use a Collection:

Dim Tmp As String
Dim s As String
Dim i As Integer
Dim colVariabili As New Collection

Tmp = "myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday"

Dim FieldStr() As String
Dim FieldSplitStr() As String
FieldStr = Split(Tmp, "&")

For Each xx In FieldStr
    FieldSplitStr = Split(xx, "=")
    colVariabili.Add FieldSplitStr(1), FieldSplitStr(0)
Next

Debug.Print colVariabili("myValue1")
Debug.Print colVariabili("someOtherValue")
Debug.Print colVariabili("HockeyDate")

It's ok if you don't have the correct sequence of var...

share|improve this answer
    
+1 Or as this is dynamic a dictionary which makes it easy to test for the existence of a key. Also I would Split(xx, "=", 2) to allow = in the value token. –  Alex K. Apr 24 at 10:47
    
Thanks, @dee got to the specific answer i wanted, however this seems like a more acceptable solution, since i wont need to include any additional addons or libraries etc. thanks! –  Nate Apr 25 at 8:51

I am not sure if this can help you, but as far as I understand your question you want to be able to create the variables dynamically based on the query string parameters. If so then here is example how to add this variables dynamically. Code needs standard module with a name 'QueryStringVariables'. In this module the query string will be parsed and each query string parameter will be added as get-property. If you wish to be able to change the value as well then you will need to add let-property as well.

enter image description here

Add reference to Microsoft Visual Basic For Applications Extensibility

Option Explicit

Private Const SourceQueryString As String = "myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday"

Sub Test()

    Dim queryStringVariablesComponent As VBIDE.vbComponent
    Dim queryStringVariablesModule As VBIDE.CodeModule
    Dim codeText As String
    Dim lineNum As Long: lineNum = 1
    Dim lineCount As Long

    Set queryStringVariablesComponent = ThisWorkbook.VBProject.VBComponents("QueryStringVariables")
    Set queryStringVariablesModule = queryStringVariablesComponent.CodeModule
    queryStringVariablesModule.DeleteLines 1, queryStringVariablesModule.CountOfLines

    Dim parts
    parts = Split(SourceQueryString, "&")

    Dim part, variableName, variableValue
    For Each part In parts
        variableName = Split(part, "=")(0)
        variableValue = Split(part, "=")(1)

        codeText = "Public Property Get " & variableName & "() As String"
        queryStringVariablesModule.InsertLines lineNum, codeText
        lineNum = lineNum + 1

        codeText = variableName & " = """ & variableValue & ""
        queryStringVariablesModule.InsertLines lineNum, codeText
        lineNum = lineNum + 1

        codeText = "End Property"
        queryStringVariablesModule.InsertLines lineNum, codeText
        lineNum = lineNum + 1

    Next

    DisplayIt
End Sub

Sub DisplayIt()
    MsgBox myValue1 'Should output "Dave"
End Sub
share|improve this answer
    
Thanks this is a great answer, however I dont want to have to include anything other than what is available. –  Nate Apr 25 at 8:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.