macro using cell value

[Jason Falzon]

Hello,

I have an application called DPlot that can take commands from excel through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above macros
assigned. This results in having to click repeatedly until I acheive the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1 values by
a cell value inside the same workbook but possibly different sheet. This cell
will be in turn controlled by a scroll bar inside excel that varies from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell and
how to use it inside each macro

 

[Andrea Jones]

x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

 

[Jason Falzon]

Thanks Andrea,

I replaced Sheet1 with the name of my sheet and put L1 instead of A1.

So now I have something like this: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,x)]"
DDETerminate Channel

End Sub

I tried using x and x.Value but the same. I am missing something. Do I need
brackets inverted commas or something?

x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

Hello,

I have an application called DPlot that can take commands from excel through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above macros
assigned. This results in having to click repeatedly until I acheive the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1 values by
a cell value inside the same workbook but possibly different sheet. This cell
will be in turn controlled by a scroll bar inside excel that varies from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell and
how to use it inside each macro

 

[Luke M]

You need some additional quotation marks to seperate the text from a
variable.
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(," & x & ")]"
DDETerminate Channel
--
Best Regards,

Luke M

Thanks Andrea,

I replaced Sheet1 with the name of my sheet and put L1 instead of A1.

So now I have something like this: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,x)]"
DDETerminate Channel

End Sub

I tried using x and x.Value but the same. I am missing something. Do I
need
brackets inverted commas or something?

x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

Hello,

I have an application called DPlot that can take commands from excel
through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above
macros
assigned. This results in having to click repeatedly until I acheive
the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1
values by
a cell value inside the same workbook but possibly different sheet.
This cell
will be in turn controlled by a scroll bar inside excel that varies
from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell
and
how to use it inside each macro

 

[Jason Falzon]

Luke and Andrea thanks a million for your help.

Finally I found an acceptable solution with your help and some research.

I finaly used 2 scroll bars one for elevation and one for azimuth. Each
scroll bar controls 1 particular cell, but both have the same macro assigned.
So when I change scroll bar macro runs automatically taking the new values

coding: -


Sub ContourView()
'
' ContourView Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
y = Worksheets("3D_Data").Range("L2").Value
Dim xNum As Long
xNum = 0
Do While xNum <> 0
Loop

Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourView(" & y & "," & x & ")]"
DDETerminate Channel

End Sub

There might be better solutions but I am more then happy with this for now.

Cheers

Jason

You need some additional quotation marks to seperate the text from a
variable.
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(," & x & ")]"
DDETerminate Channel
--
Best Regards,

Luke M

Thanks Andrea,

I replaced Sheet1 with the name of my sheet and put L1 instead of A1.

So now I have something like this: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
x = Worksheets("3D_Data").Range("L1").Value
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,x)]"
DDETerminate Channel

End Sub

I tried using x and x.Value but the same. I am missing something. Do I
need
brackets inverted commas or something?

x = Worksheets("Sheet1").Range("A1").Value

www.stratatraining.co.uk

:

Hello,

I have an application called DPlot that can take commands from excel
through
a macro.

I managed to pass information like so: -

Sub DownElevation()
'
' DownElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,-1)]"
DDETerminate Channel

End Sub
Sub UpElevation()
'
' UpElevation Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(,1)]"
DDETerminate Channel

End Sub
Sub LeftAzimuth()
'
' LeftAzimuth Macro
' Macro recorded 24/03/2010
'

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(-1,)]"
DDETerminate Channel

End Sub
Sub RightAzimuth()
'
' RightAzimuth Macro
' Macro recorded 24/03/2010

'
Channel = DDEInitiate("DPlot", "System")
DDEExecute Channel, "[ContourViewChange(1,)]"
DDETerminate Channel

End Sub

At the moment I created 4 buttons, each of which have 1 of the above
macros
assigned. This results in having to click repeatedly until I acheive
the
disired result.

Now I'd like to make it moredynamic by substituiting the 1 and -1
values by
a cell value inside the same workbook but possibly different sheet.
This cell
will be in turn controlled by a scroll bar inside excel that varies
from -180
to 180.

My problem is that I cannot figure out the coding to refer to the cell
and
how to use it inside each macro

.