Need help with four basic VBA functions

[chuchugaga]

Hi everyone,

I'm a newbie at VBA and I'm trying to get the hang of it. I have to learn how to do the following in preparation of an upcoming exam:

1. Write a function which takes in an input n and outputs the nth odd number


2. Write a function which takes in a number and outputs its factorial


3. Write a macro which takes an input value from cell a1 and changes the font color of column b


4. Write a macro that takes the range values as a string such as "a1:a15" and sums all the values

While I'm pretty comfortable with Excel and recording macros in general, coding in VBA is uncharted territory for me and I'm not sure I understand all the syntax (think they could've made it simpler, to be honest!)
Can anyone help me with the above 4 tasks? It would of course be of no use if I didn't learn from it, so if possible, please take some time out and explain to me why a particular syntax has been used and which piece of code does what. This way, I can try my own hand at it and see how it goes.

Cheers
Usama

[Simon Lloyd]

IMHO i suggest that you approach your tutor, if you haven't grasped how to do any of the above then you'll not understand any help we provide, your tutor is best placed to help you here as he/she knows the coursework and your individual capabilities. Once you have a better starting knowledge come back to us with your efforts and we'll point you in the right direction but without actually giving you the solution (just as your tutor would do )

[chuchugaga]

IMHO i suggest that you approach your tutor, if you haven't grasped how to do any of the above then you'll not understand any help we provide, your tutor is best placed to help you here as he/she knows the coursework and your individual capabilities. Once you have a better starting knowledge come back to us with your efforts and we'll point you in the right direction but without actually giving you the solution (just as your tutor would do )

Hi Simon,

Thanks for the response. I went through some basic VBA coding guides and actually managed to do the first two of those on my own

I'm now trying to figure out part 3, and so far I've used the record macro tool to come up with this:

Sub Macro1()
'
' Macro1 Macro

Dim FontColor as Double
FontColor.Value = ("A1")
   Columns("B:B").Select
    With Selection.Font
        .Color = FontColor
        .TintAndShade = 0
    End With
End Sub

And in cell A1 I have the color code -16711681. I've tried different variations of this but I can't seem to get it to work, probably because of the FontColor.Value = ("A1") line. Could you point me in the right direction please? I do understand that the actual value lies in figuring it out on your own, but I think I'm more than halfway there in this case and just need a little push on.

Thanks,
Usama

[SamT]

Excel formulas expect A1 style addresses to refer to a Range value, but you have to specifically tell VBA that it's a Range and you have to tell it what thing you want from the Range.

Range("A1").Value

Hint: Type the word "Range" in a VBA code page, place the cursor in or next to "Range" and press F1. Then click the "Properties" link at the top of the page that comes up. Those are all the things you can get from, (and give to,) a Range.

[Simon Lloyd]

Hi Simon,

Thanks for the response. I went through some basic VBA coding guides and actually managed to do the first two of those on my own

I'm now trying to figure out part 3, and so far I've used the record macro tool to come up with this:

Sub Macro1()
'
' Macro1 Macro

Dim FontColor as Double
FontColor.Value = ("A1")
   Columns("B:B").Select
    With Selection.Font
        .Color = FontColor
        .TintAndShade = 0
    End With
End Sub

And in cell A1 I have the color code -16711681. I've tried different variations of this but I can't seem to get it to work, probably because of the FontColor.Value = ("A1") line. Could you point me in the right direction please? I do understand that the actual value lies in figuring it out on your own, but I think I'm more than halfway there in this case and just need a little push on.

Thanks,
Usama

Thanks for your attempt (and BTW that code isn't produced by the macro recorder!), you're nearly there, you dont need to declare fontcolor you can just use the range value and you dont need tint and shade, the code i've given here is pretty much yours but it goes in the worksheet code module, every time you click in column B the fonts there will change colour to that in A1 of the same sheet, have a play around with it to get it to work as you need.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'check we are only running this if we ar clicking column 2
If Target.Column = "2" Then
'check that it IS a number in A1 before running the macro
    If IsNumeric(Me.Range("A1")) Then
    Columns("B:B").Select
        With Selection.Font
            .Color = Range("A1").Value
        End With
    End If
End If
End Sub

You should look at handling errors in your code too http://support.microsoft.com/kb/141571

[Uva]

Can you please send me the VBA coding of all the questions which you posted just for practice.

[SamT]

Chuchugaga,

For your consideration, compare this to Simon's excellent example.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

     'check we are only running this if we are clicking column 2
    If Not Target.Column = 2 Then Exit Sub

    'check that it IS a number in A1 before running the macro
    If Not IsNumeric(Range("A1")) Then Exit Sub
    
    Target.EntireColumn.Font.Color = Range("A1").Value
End Sub

[Simon Lloyd]

Much better than SELECTION Sam, i left the selection as it was so the OP could understand how the code was similar to his own code, that said he/she should be able to get to grips with that now and see an improvement betqween both codes.

[SamT]

Simon is not Paul. Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul. Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul. Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul.Simon is not Paul. Simon is not Paul. Simon is not Paul.

[snb]

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.Column = 2 and IsNumeric(Range("A1")) Then Columns(2).Font.Color = Range("A1").Value 
End Sub

[Simon Lloyd]

@Sam It's an easy mistake, they're so alike