View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc,comp.lang.basic.visual.misc
Dave Peterson Dave Peterson is offline
external usenet poster
 
Posts: 35,218
Default A few Question Concerning VBA in Excel

SaveData is expecting any kind of variable.
Macro1 is passing the range correctly to A1, A2, and A3. But as soon as you
reassign A1=9, then A1 isn't the range anymore. It's just the plain old scalar
value 9.

You could have used
a1.value = 9
in SaveData and had the same effect as SaveData1 and macro1_1

======
You could use:
Const Zero as Long = 0
or
Dim Zero as long
Zero = 0

but why not just use the value 0.

Sub Save_Unit_Qunatities(A1 As Range, A2 As Range, A3 As Range, A4 As Range, _
A5 As long, A6 As long, A7 As long)

or
Sub Save_Unit_Qunatities(A1 As Range, A2 As Range, A3 As Range, A4 As Range, _
A5 As double, A6 As double, A7 As double)

And just a guess...

with PressDocking1A_Suit_Docking_Qty_V
.numberformat = "General"
.value = whatevervalueyouwanthere
end with

If the cell were formatted as text, then formatting as general first will allow
the value to be a real number.

wrote:

In the following the "Macro1" does not work while "Macro1_1" does. I
know I got a method to make Range("VarName") update while being a
parameter in a subroutine. But could you explain to me why and if I
have done it correctly.

Option Explicit

Sub SaveData(A1, A2, A3)
A1 = 9
A2 = 8
A3 = 7
End Sub

Sub macro1()
Range("name1") = "'"
Range("name2") = ""
Range("name3") = ""
Call SaveData(Range("name1"), Range("name2"), Range("name3"))
End Sub

'--- BOTTOM ONES WORK THE TOP ONES DON'T

Sub SaveData_1(A1 As Range, A2 As Range, A3 As Range)
A1 = 9
A2 = 8
A3 = 7
End Sub

Sub macro1_1()
Range("name1") = "'"
Range("name2") = ""
Range("name3") = ""
Call SaveData_1(Range("name1"), Range("name2"), Range("name3"))
End Sub

[Second question I have a code example]

Dim Zero As Range

Sub Save_Unit_Qunatities(A1 As Range, A2 As Range, A3 As Range, A4 As
Range, A5 As Range, A6 As Range, A7 As Range)

Set Zero = Range("Zero") 'Zero is a reference variable that is 0

[Is there a way I could assign a range variable such a Zero like this
Zero = 0 ]

Call Save_Unit_Qunatities(Range("PressDocking1A_Suit_Do cking_Qty_V"),
Range("PressDocking1A_Rover_Docking_Qty_V"),
Range("PressDocking1A_Vehicle_Docking_Qty_V"), Zero, Zero, Zero, Zero)

[I have to use Zero because the problem is I can't do the following: ]
Call Save_Unit_Qunatities(Range("PressDocking1A_Suit_Do cking_Qty_V"),
Range("PressDocking1A_Rover_Docking_Qty_V"),
Range("PressDocking1A_Vehicle_Docking_Qty_V"), 0#, 0#, 0#, 0#)

[And final question when I make this call]
Call Save_Unit_Qunatities(Range("PressDocking1A_Suit_Do cking_Qty_V"),
Range("PressDocking1A_Rover_Docking_Qty_V"),
Range("PressDocking1A_Vehicle_Docking_Qty_V"), Zero, Zero, Zero, Zero)

The reference cell "PressDocking1A_Suit_Docking_Qty_V" takes on
numbers but there "numbers stored as text." I there a way I can save
them as numbers?

I would really appreciate any help in this matter,

Thanks,


--

Dave Peterson