Excel Macro Problem - Formula Replacement

Pathis7 · Oct 9th 2003

I am having a problem with creating an Excel spreadsheet that uses DDE to put information from another program into specific cells in Excel.

I am creating buttons and assigning the macro to it, so when the button is clicked, the macro is executed.
The macro is along the lines of this:

Sub GetMyItem()
Sheets("Sheet1").Cells(27,7).Formula = "=MyProgram|MyTopic!'MyItem'"
End Sub

This works fine, in that I will execute the macro and the desired item information will go directly into cell 27,7. But the item in MyProgram will change, and I want to put each item in the SAME Excel spreadsheet. So then I create other macros with the cell different, but the rest the same since it is the same Item:

Sub GetMyItem2()
Sheets("Sheet1").Cells(29,7).Formula = "=MyProgram|MyTopic!'MyItem'"
End Sub

and on and on...

But since this basically puts a formula in the cell, which is updated any time I press the button, ALL of the Items are updated to the Current Item in MyProgram. That is, if The item in MyProgram was 12, and I call GetMyItem, it puts 12 in cell 27,7. But then the item changes to 23, and when I call GetMyItem2, BOTH cell 27,7 AND 29,7 will change to 23. I believe this is normal for Excel, but not what I want.

I think what I need is for each macro to retrieve the value from MyProgram|MyTopic!'MyItem' and store it in a variable X. Then put X in the prescribed cell. How do I do this?? Urgent help is needed.

Barrie Davidson · Oct 9th 2003

That makes sense because it's the formula you are using references the same thing. What about changing your code to paste values? Something like:

Code

Sub GetMyItem()
Sheets("Sheet1").Cells(27, 7).Formula = "=MyProgram|MyTopic!'MyItem'"
Sheets("Sheet1").Cells(27, 7).Copy
Sheets("Sheet1").Cells(27, 7).PasteSpecial xlPasteValues
End Sub
Sub GetMyItem2()
Sheets("Sheet1").Cells(29, 7).Formula = "=MyProgram|MyTopic!'MyItem'"
Sheets("Sheet1").Cells(29, 7).Copy
Sheets("Sheet1").Cells(29, 7).PasteSpecial xlPasteValues
End Sub

Anonymous · Oct 9th 2003

You might try
Sub GetMyItem()
Sheets("Sheet1").Cells(27,7).Value = MyProgram|MyTopic!'MyItem'
End Sub

or whatever form MyProgram|MyTopic!'MyItem' should be in to actually get the value back to your macro.

Or, if that is not obvious what to change then the following should work.

Code

Sub GetMyItem()
    With Sheets("Sheet1").Cells(27, 7)
        .Formula = "=MyProgram|MyTopic!'MyItem'"
        .Value = .Value
    End With
End Sub

Ooops, I see barrie gave a variation of the above.

Pathis7 · Oct 9th 2003

Derk, your first suggestion did not work as specified, however your second suggestion, which I guess is akin to Barrie's solution, worked. In any case, I am very pleased you both responded with such accurate solutions so quickly.

It works now. I can update the cells individually.

But I am curious now. Is there anyway to have this done in just one click of the button? Currently, I have to click twice, sometimes even 3 times to get the proper update (never more than 3 times though).

Thanks again!

Anonymous · Oct 9th 2003

I don't know why something would take several clicks. What button are you clicking? A button to run the macro? Or F9 to recalculate? or ... ?

Pathis7 · Oct 9th 2003

yes. a command button assigned to the before-said macro. It usually takes only one click and now the copy-special paste enhanced macro ensures 2-3 clicks, sometimes displaying intermittent values (the previous item value). But always on the third click it is the correct value.

Anonymous · Oct 10th 2003

It may be that the value of the cell is being fixed before the called data has a chance to be returned. Try pausing the macro for a bit and see if that cures the problem. The following should give a 2 second pause for the data to be retrieved. It may take more or less; try experimenting.

Code

Sub GetMyItem()
    With Sheets("Sheet1").Cells(27, 7)
        .Formula = "=MyProgram|MyTopic!'MyItem'"
        Application.Wait Now + 2 / (24 * 60 * 60#) 'wait 2 seconds
        .Value = .Value
    End With
End Sub

Pathis7 · Oct 10th 2003

The idea is right, in that adding a delay reduces the number of clicks to 2. Now two clicks will retrieve the correct data, but the wait is truly long for the purpose.

EDIT: I was wrong, hasty clicking makes it three. The wait actually did not really do anything. Any idea why it takes calling the macro twice completely, to get the correct values? It seems there is a buffer somewhere in Excel that is storing the values I get from MyProgram. The buffer starts out empty, so the first time I click (run the macro) I get N/A, but then the values Excel receives are immediately put in this buffer. So the next time I run the macro, the values from the buffer (the previous values) are put in place and the next set of values are taken from MyProgram and put in the buffer.

Sorry for the crude analysis, but this is just what appears to be happening.

I tried adding the same instructions twice in the same macro, but there were no differences. Any ideas?

Excel Macro Problem - Formula Replacement

Participate now!

Share