Sending a VB variable from a module to Excel Spreadsheet Cell

[Beavoid]

How can I send a variable theta_eff from a VB module to a spreadsheet cell named Theta.

Obviously, I am new to this program I am using v 6.3.

Thanks anybody!

B

[si_the_geek]

Thread moved to Office Development/VBA forum (note that the "VB Editor" in Office programs is actually VBA rather than VB, so the VB6 forum is not really apt)

[westconn1]

try like
range("Theta") = theta_eff

[Beavoid]

Here is what I am trying, with no results so far (no errors either), but that could be because I haven't yet translated all of the rest of the code yet:

Function BearingOutputs() As Double
Worksheets("Outputs").Range("Theta").Value = theta_eff
Worksheets("Outputs").Range("EccentricityRatio").Value = ecc
Worksheets("Outputs").Range("AttitudeAngle").Value = att

Where Outputs is the name of the worksheet tab within the file (should it be the filename, instead?). Do I have to place anything else in the function call?

Also, do I have to do anything to the Theta Excel cell in order to make it take the sent variable value?

Thanks for the help!

B

[si_the_geek]

What you have got should be putting values into the cells, but what values depends on the variables - so check what theta_eff actually contains when that code runs.

[westconn1]

if the range "Theta" etc is a defined name AND the variable theta_eff, is in scope and contains a value, then i can see no reason why it should not work

as the variables are not declared within the function (or at least the part i can see) where are they declared and their values set?

[Beavoid]

Variables are declared in the previous function in the module. I would assume I have to call them, then, yes?

B

[si_the_geek]

If the variables are declared inside a sub/function, they are only available inside that sub/function.

To share variables between different routines, declare them in the General-Declarations section of the code (at the very top, before any subs/functions) using the keyword Private or Public.

For further explanation, see the article What is the difference between Dim/Private/Public/Global/Static/Const? from our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

[Beavoid]

Function BearingOutputs(theta_eff, ecc, att, som, pressure_average, dof, dfc, clearance_eff, loss, flowTotal, peakTemp, deltaTheta, loss) As Double
    
'Variables to be sent to Excel

    Worksheets("Outputs").Range("Theta").Value = theta_eff
    Worksheets("Outputs").Range("EccentricityRatio").Value = ecc
    Worksheets("Outputs").Range("AttitudeAngle").Value = att

That's a snippet of the code. I have, for example, a cell named Theta, but nothing is going to it at the moment. I believe this is just due to the fact that the previous function isn't complete, but is there anything else you can see that is missing in order for, say, the value of theta_eff, to be sent to Excel cell Theta?

Thanks for all the help.

[Beavoid]

Public is how I had them declared to begin with, before I started tinkering and getting aggrevated because I didn't know what was going on haha.

Thanks

[westconn1]

you can test the above code by putting a literal value
Worksheets("Outputs").Range("Theta").Value = 123

if that works you know the problem is with your variables

public variables must be declared at the top of the code module in the general section

[Beavoid]

Are you sure it is possible to use .Range for a single cell named Theta?

Here is what I have:

 Function BearingOutputs() As Double
    'Variables to be sent to Excel
    Worksheets("Outputs").Range("Theta").Value = 555
End Function

I am sending it to a worksheet titles Outputs to a cell entitled Theta and nothing happens

Any ideas?

Thanks for all the help

B

[Beavoid]

I think the problem is definitely using a function instead of a subroutine. I thought using Public variables would make this unnecessary, but I think I was wrong, so I am redoing it now and it seem promising so far.

Thanks for the help!

B

[Siddharth Rout]

Is this what you are trying to do?

'~~> Run This
Sub Test()
    Dim Ret
    Ret = BearingOutputs()
End Sub
 
Function BearingOutputs()
    '~~> Variables to be sent to Excel
    Worksheets("Outputs").Range("Theta").Value = 555
End Function

[westconn1]

how did you define the cell theta?