Note: So far, I haven't found a way to supply a DataSet through the web service and consume it, I've basically hacked and slashed my way through figuring this out.  VB programmer I ain't.

First create your web service:

  [WebService(Namespace="MSTK.Web.Services")]
  public class Model : System.Web.Services.WebService
  {
    /// <summary>
    /// Get a listing of model information.
    /// </summary>
    /// <returns></returns>
    [WebMethod]
    public string GetModelName()
    {
      return "Super Duper Model";
    }
  }
Yeah, pretty boring, but this is just an example. 
 
Now open up MS Excel.  Tools->Macro->Visual Basic Editor.
 
Right click on the "Modules" folder thingy in the Project's dockable window (usually on the right?)  Insert->Module.  It'll create Module1
 
Do the same for forms.  Click Insert->UserForm.  It'll add UserForm1.
 
On your form, drag and drop a listbox onto it.  Resize it a bit.  I left my listbox's name = to ListBox1.
 
Double click your module and enter this crap:
Sub ShowForm()
  UserForm1.Show
End Sub
 
Now in your form.  Right click it, click "View Code".  Paste this:
 
Private Sub UserForm_Initialize()
    Dim oSOAPClient As Object
    Set oSOAPClient = CreateObject("MSSOAP.SoapClient")
    oSOAPClient.mssoapinit "http://localhost/SATK/SATK.Web.Services/model.asmx?wsdl", "Model", "ModelSoap"
    Dim arrTemp As String
    arrTemp = oSOAPClient.GetModelName()
    Me.ListBox1.AddItem (arrTemp)
End Sub
 
Only two lines to pay attention to here.  Lets look at the first:
 
oSOAPClient.mssoapinit "http://localhost/SATK/SATK.Web.Services/model.asmx?wsdl", "Model", "ModelSoap"
 
If you created your web service with VS.IDE (if you didn't...uh...stop reading) you should be able to surf to the web service URL and see Model and ModelSoap in there someplace.
 
"Model" refers to your class.  Set it to the class that you're using as a webservice.  "ModelSoap" uh...I dunno I put it there and it worked.
 
Next important line:
 
arrTemp = oSOAPClient.GetModelName()
 
This method within my web service just returns a string.  Look at the web service method above if you don't believe me.
 
Sure enough this'll add "Super Duper Model" to the listbox.
 
Now to run it:
1) Press F5 in the Visual Basic Editor (F8 to step through stuff)
2) Back in Excel you can click Tools->Macro->Macros and then select the Macro you wanna run (ShowForm)