Linking VBA code to spreadsheet
 

That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

Linking VBA code to spreadsheet
 

Why doesn't it help you much Mark?

You asked how to pick up a value from a spread sheet.

you refer to a value on a spreadsheet with

Worksheets("sheet1").Range("b1").Value

you put a value in a spreadsheet with

Worksheets("sheet1").Range("B7").Value = 21


you can store the values in a variable.

vVal = Worksheets("sheet1").Range("b1").Value

Worksheets("sheet1").Range("b1").Value = vVal ^ 2

Not sure what else you would need to know.

--
Regards,
Tom Ogilvy


mark_varney47 wrote in message
...
That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

Linking VBA code to spreadsheet
 

VBA can "pick up" and "put back" spreadsheet data in several ways. A
few among them:

You can refer to the cells directly:

Sheets("Sheet1").Range("B7").Value = _
Sheets("Sheet1").Range("B1") + Sheets("Sheet1").Range("B2")

which can be shortened to

With Sheets("Sheet1")
.Range("B7").Value = .Range("B1").Value + .Range("B2").Value
End With

or, if Sheet1 is the activesheet, just

Range("B7").Value = Range("B1").Value + Range("B2").Value

You can assign intermediate variables:

Dim myVar1 As Double
Dim myVar2 As Double
Dim myVar3 As Double

myVar1 = Sheets("Sheet1").Range("B1")
myVar2 = Sheets("Sheet1").Range("B2")
myVar3 = myVar1 + myVar2
Sheets("Sheet1").Range("B7").Value = myVar3

You can assign object variables to refer to the cells:

Dim myCell1 As Range
Dim myCell2 As Range
Dim myCell3 As Range
Set myCell1 = Sheets("Sheet1").Range("B1")
Set myCell2 = Sheets("Sheet1").Range("B2")
Set myCell3 = Sheets("Sheet1").Range("B7")
myCell3.Value = myCell1.Value + myCell2.Value

You can also capture a contiguous range of data into a Variant
variable with a 2-D array:

Dim myRng As Variant
myRng = Sheets("Sheet1").Range("B1:B6").Value
Sheets("Sheet1").Range("B7").Value = _
myRng(1, 1) + myRng(2, 1) - myRng(6, 1)

In article ,
mark_varney47 wrote:

That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

Linking VBA code to spreadsheet
 

post yer code.
"mark_varney47" wrote in message
...
That really does not help me that much ,unfortunately.I already have the
vba code to do the calculations.What I need to know is how I can get
this code to pick up values from an Excel spreadsheet.The input data
would be in cells,B1,B2,B3,B4,B5 and B6.The vba code would take this
data and produce a single result to cell B7.How do I get vba to pick up
this data in the spreadsheet and then put the result back in the
spreadsheet in cell B7.I know that it is a simple question,but I am
stuck and not making much progress.Is there a specific method of
linking a spreadsheet to code in the code editor??

Linking VBA code to spreadsheet
 

Bradley/Tom or anyone else that can help,
This is the code that I am trying to link to my spreadsheet.

Public Function Range("B1").Value As String,Range("B2"). _
Value As Double,Range("B3").Value As Double,Range("B4"). _
Value As Double,Range("B5").Value As Double,Range("B6"). _
Value As Double) As Double

Dim1 d1 As Double, d2 As Double
d1=(Log(B2/B3)+(B6^2/2)*B4)/(B6*Sqr(B4))
D2=(D1-B6*Sqr(B4)

If B1="c" Then _
B7=Exp(-B5*B4)*(B2*CND(d1)-B3*CND(d2))
If B1="p" Then _
B7=Exp(-B5*B4)*(B3*CND(-d2)-B2*CND(-d1) _
ElseIf _

End Function

The values to be input into the spreadsheet are as follows:
B1=c
B2=3900
B3=3900
B4=0.5
B5=0.05
B6=0.25
B7=Blank as this is where the output should appear.


Any help with this would be gratefully appreciated.
Thanks,
Mark

Linking VBA code to spreadsheet
 

Can anyone help me with this spreadsheet/VBA problem???
Many Thanks,
Mark Varney