A few Question Concerning VBA in Excel


B

bjdesa

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_Docking_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_Docking_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_Docking_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,
 
Ad

Advertisements

D

Dave Peterson

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.

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_Docking_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_Docking_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_Docking_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,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top