asked on

VBA programme simple exercise doesn't work

I am at long last trying to learn VBA, and have a Kindle version of Excel VBA Programming for Dummies by John Walkenbach. I have followed exactly the instructions he gives for creating the simplest of macros to create my name and the date, in bold text and 16pt. I followed the instructions EXACTLY, and do not get the expected result. I attach my spreadsheet with this Question, and also a screenshot of the Visual Basic Editor shown in the Dummies book.
What have I done wrong? I want to be able to go through this book and learn VBA, but with this bad start I am already foxed.
Any ideas?Macro-Lesson-No.-1.xlsm

Norie

What exactly isn't working/going wrong?

ASKER CERTIFIED SOLUTION

BlosMusic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Roy Cox

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roy Cox

Sorry, I didn't notice the shortcut but this works for me

Sub NameAndDate()
'
' NameAndDate Macro
' My name and date
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.FormulaR1C1 = "Richard Cherry"
    ActiveCell.Offset(1).Value = Format(Now, "DD/MM/YY HH:MM")
    With Range(ActiveCell, ActiveCell.End(xlDown)).Font
        .Name = "Calibri"
        .Size = 16
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End Sub

Open in new window

BlosMusic

ASKER

Hi All,
Thanks for the comments. It isn't that I need to solve this problem for its own sake - more that I'm maybe missing something obvious and am not getting the result the book said I should get.
If you see the attachment, which is the chapter in question in the book, and see if you get the result that i did, then the book's got something wrong - otherwiseVBA-book-screenshot.pdf it's me, or my Excel settings . . . .

Roy Cox

The book isn't incorrect. The problem that you have is that the code is recorded. The Macro Recorder will record each step exactly as you do it. So you end up with unnecessary code. The recorded code should be edited because it had the cell address is hard coded and contains unnecessary coding, I have posted code showing how I would edit it, I'm not sure how John Walkenbach would handle

Gustav Brock

Main problem is that recorded macros always operate with selections where you should use precisely specified objects and the much faster ranges.

Thus, 99% of recorded macros can only serve as a guide to which properties and actions to take; then rewrite this from scratch.

/gustav

BlosMusic

ASKER

Thank you all. I shall study this (as and when time permits) and revert at a later stage. I am grateful for the input