Vba tips and help

[paul]

I am taking an exam as part of my degree in september and i was
looking through a past paper of vba and didnt understand at all how to
answer this question??

please help


When using VBA with excel it is important to be able to read and
write to the excel spreadsheet. Write VBA commands to perform the
following tasks:

a/ Place in the variable Y the number in the cell A6 of the currently
active worksheet (4 marks)

b/ Place in the cell A7 the text "Hello world" (4 marks)

c/ Place a message box on the screen giving the message contained in
the string variable, Prompt$ and asking for a yes, no or cancel reply
(5 marks)

d/ Read the area designated by the string variable Area$ into a one
dimensional array called Input() (4 marks)

e/ Read the 5th column and 3rd row of the currently active spreadsheet
into the array element A(2,4) (4 marks)

f/ Read the value in the cell B7 from worksheet, sheet6 into the
variable, Step (4 marks)

 

[Bob Phillips]

We don't do homework, just try to help specific problems.

If you don't know how to do these things, what do you know, what are you
thinking about these questions (re solutions), and what do you need pointers
with?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[ali]

I am taking an exam as part of my degree in september and i was
looking through a past paper of vba and didnt understand at all how to
answer this question??

please help

When using VBA with excel it is important to be able to read and
write to the excel spreadsheet. Write VBA commands to perform the
following tasks:

a/ Place in the variable Y the number in the cell A6 of the currently
active worksheet (4 marks)

b/ Place in the cell A7 the text "Hello world" (4 marks)

c/ Place a message box on the screen giving the message contained in
the string variable, Prompt$ and asking for a yes, no or cancel reply
(5 marks)

d/ Read the area designated by the string variable Area$ into a one
dimensional array called Input() (4 marks)

e/ Read the 5th column and 3rd row of the currently active spreadsheet
into the array element A(2,4) (4 marks)

f/ Read the value in the cell B7 from worksheet, sheet6 into the
variable, Step (4 marks)

HI

a/
dim Y as integer
Y=RANGE("A6").Value

b/

dim Y as string
y="hello world"
range("a7")=y

c/

Dim Y As VbMsgBoxResult

Y = MsgBox("hello world", vbYesNoCancel)

i hope it helps.

cheers

ali

 

[paul]

Yeah I understand that, my lecturer isnt very useful and hasnt replied
to me, beacuse this is from a past pa


per im looking at forn revision purposes but never came across these
questions and havnt got a clue what to do.

I was jsut wondering if any one could helpm explain how tot ackle a
question like this.

 

[SeanC UK]

Hi Paul,

Firstly, I would seriously consider attempting these questions, as they are
relatively simple, and shouldn't take you long to understand and solve. Also,
as they are fairly simple, I'm guessing it's the basic concepts rather than
the solutions that they are hoping you understand.

a/
You have been told to expect a number, so, if you were being thorough, you
should check the cell contents are numeric. Make the variable one that uses
the least memory as possible. Ali suggest an Integer, but you're not told the
number type in the question, so Double would be safer.

Dim Y As Double
Y = ActiveSheet.Cells(6, 1)

b/
You can get away without using variables here:
Cells(7, 1) = "Hello World"

c/
I think the $ on the variable has come from older programming languages,
although it still works in VBA (as I have just found) to declare that the
variable is of the string type. Although I think that it is now more common
to see

Dim Prompt As String
Prompt = "Some Prompt"
Call MsgBox(Prompt, vbYesNoCancel)

Using Call means you don't need to declare a variable to accept the return
from MsgBox.

d/
Again, I'll declare a string and omit the $.

Dim Area As String
Dim myCell As Range
Dim arrInput() As Variant - variant keeps cell contents in their own data
types
Dim Counter As Integer - integer as I know only 16 cells in my range
Area = "A14"
Counter = 0
For Each myCell In Range(Area)
ReDim Preserve arrInput(Counter)
arrInput(Counter) = myCell.Value
Counter = Counter + 1
Next

e/
You don't always need to use ActiveSheet when collecting data etc from the
active sheet. However, it is best to do so as it removes ambiguity, and will
help reduce errors (readers of your code will know that you meant the active
sheet)

Dim myArr2(2, 4) As Variant
myArr2(2, 4) = ActiveSheet.Cells(3, 5)

f/
Don't use Step as this is a common command in most languages for loop control.
Dim NotStep As Variant
NotStep = Worksheets("sheet6").Cells(7, 2)


Where possible, always use a data type that best suits the data you want,
and test the data in the cells before trying to collect it so as to prevent
errors.

Just out of interest, what degree are you studying?

Sean.

 

[paul]

In answer to your question Im studying a business degree with GIS
(Geographical Information Systems) and one module this year and next
year are on intro to programming. Which we use all about VBA














Hi Paul,