Very basic VBA

I have downloaded Excel VBA Programming for Dummies. I tried the first example "Recording a Macro", in which you get to try a macro which uses Ctrl+Shift+N to simply put your name and the date in any box at the click of a (Ctrl+Shift+N)! It worked the first time, and when I studied what I did, and tried it again - it wouldn't work. I have persevered for two days (off and on), and am going mad. What on earth is happening? I am following the "Dummies" book diligently, but it just doesn't work any more! What's gone wrong?
Failed-Dummies-VBA-exercise.xlsm
BlosMusicAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
It works when I run it and also if I run it a second time.
0
rspahitzCommented:
I suspect that the Ctrl+Shift+N is already connected to your previous macro.  If you erased, it, it may be pointing to nothing.

What I would suggest for now it to record without using the key-shortcuts.  When done, go to the macros window (Alt+F8) to see the macro(s) you've created, and run them from there.

FYI
I've written a yet-to-be-released eBook on Office Macros. If you're interested in getting a copy when I release it, check my profile and e-mail me.
0
FaustulusCommented:
There are two subjects to your question, not one.
1. You record a macro
Once you saved the workbook in which you recorded the macro it exists within this workbook forever, but not in any other workbook. If you didn't save the macro it doesn't exist. I think you know how to check that.
2. You run the macro you have recorded.
There are many ways of running a macro.
You can locate the macro in the VB Editor, place the cursor anywhere within the macro code and press F5.
Or you can press the Macros button on the Developer tab, select your macro in the list and press Run.
Or you can create a keyboard shortcut, like Shift+Cntrol+N
The shortcut must be saved to the workbook like any other change, else it will be lost when you close the workbook. In my experience (Excel 2003) they get lost anyway. So, I stopped using them. Now I use Excel 2010 but still don't trust keyboard shortcuts. I'm very comfortable not using them.
However, for the stage you are at, I understand that you want to make it work and presume that it is a matter of saving your work.
From the Developer tab/Macros, select the Macro in the list and check the shortcut assigned to it. If there is none you can set one. Remember to Save.
I'm n an iPad right now and couldn't see your workbook. I'll look at it later and might add to my comment. Meanwhile, I suppose speed is of the essence for you and I hope I have been of use.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Martin LissOlder than dirtCommented:
BTW if all you want to do is the have your name appear in the active cell and have the bolded date below it, all you need do is this.


    ActiveCell.Value = "RichardCherry"
    ActiveCell.Offset(1, 0).Value = "=NOW()"
    ActiveCell.Offset(1, 0).Font.Bold = True

Open in new window

which as I'm sure you will learn can be writtm more efficiently this way.

    With ActiveCell
        .Value = "RichardCherry"
        .Offset(1, 0).Value = "=NOW()"
        .Offset(1, 0).Font.Bold = True
        End With
    End Sub

Open in new window

0
Martin LissOlder than dirtCommented:
If you want to bold both items then do this.


    With ActiveCell
        .Value = "RichardCherry"
        .Offset(1, 0).Value = "=NOW()"
        Range(.Address, .Offset(1, 0).Address).Select
        With Selection.Font
            .Name = "Calibri"
            .Size = 16
            .Bold = True
        End With
    End With

Open in new window

0
Brian PiercePhotographerCommented:
0
Martin LissOlder than dirtCommented:
You didn't describe what you mean when you said it didn't work but is this what happens? You run it once with a clean sheet and it works, but then you try selecting another cell and when you run the macro all that seems to happen is that your name gets placed in the newly selected cell but no date or bolding? That's happening because in the macro the cells for the date and the bolding were "hard coded" by the macro recorder so the same cells get the date and bolding every time the macro is run.
0
FaustulusCommented:
@BlosMusic,
Your workbook has the keyboard shortcut saved to it and it works just fine. When you test it be sure to delete everything you have in your worksheet before you run the macro because otherwise the impression will be created that nothing happened.
The macro recorder isn't a tool to create ready macros for you. It can show you how to do things. But once you know how to do them you must do them yourself, in your own way, not the recorder's way, That is why you need to learn how to read the recorder's code. Toward this end I have commented the code in your workbook. Here it is:
Sub NameAndTime()
    ' Which is the ActiveCell?
    ' This isn't controlled by the macro.
    ' The Activecell is the cell you last clicked before
    ' starting this program
    
    ' The macro writes to the cell previously selected
    ActiveCell.FormulaR1C1 = "RichardCherry"
    
    ' Now the macro selects a new cell
    Range("G14").Select     ' G14 is now the ActiveCell
    
    
    ActiveCell.FormulaR1C1 = "=NOW()"   ' the macro writes a value to G14
    
    ' Now the program selects a new cell
    Range("G14").Select                 ' actually, it is the same as before
    
    Selection.ClearContents             ' the macro deletes what it just wrote
    
    ActiveCell.FormulaR1C1 = "=NOW()"   ' now it writes the same thing again
    
    ' Now the program selects a new cell
    Range("G14").Select                 ' actually, it is the same as before
    
    ' Observe: Because G14 was selected it is the ActiveCell
            '  But because it was "selected" it is also the "Selection"
    Selection.Copy     ' the value of G14 is copied to the clipboard
    
    ' the Paste command pastes the contents of the clipboard
    ' to the range specified, in this case "Selection"
    ' So, the contents of the clipboard (which is G14) is pasted
    ' to the Selection (which is also G14)
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ' Now the program selects a new cell
    Range("G13:G14").Select    ' this time a range comprising of 2 cells is selected
    ' In a selected range ("Selection") the first cell is ActiveCell by default
    ' The first cell is G13
    
    ' Now the ActiveCell is changed from G13 to G14
    Range("G14").Activate
    ' This change is of no consequence because with the next
    ' line of code you address not the Activecell but the Selection
    ' The Selection is still G13:G14
    
    ' Now the macro changes the formatting of the Selection
    Selection.Font.Bold = True
    With Selection.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
    
    
    ' Now the program selects a new cell
    Range("H13").Select
    ' This selection seems to be of no particular importance
    ' unless you run the same procedure again
    ' in which case H13 will be the ActiveCell
    ' in which "RichardCherry" will be written.
End Sub

Open in new window

Now you should be bold enough to delete all those rows that don't contribute toward your intention. Don't be afraid to run the stripped version. It will do exactly what you want which, after all, was the object of the exercise.
0
rspahitzCommented:
@Blos...

While the above experts have given correct information, I can see how it might be overwhelming for a beginner.  Don't worry about all of those things right now.

Close Excel (all books to make sure nothing gets help in memory, although that shouldn't apply to macros anyway).
Open a new workbook.
Record a new macro (omitting the keyboard shortcuts at this point.)
Re-run the macro through the menus (Alt+F8 or menu View | Macros | Macros | View Macros.)
As needed, clear the sheet or proceed to a different sheet.
Re-run the macros again.


Once you get comfortable with this process, you can go back and learn the other parts.
0
BlosMusicAuthor Commented:
Thank you all! All very helpful (and erudite, for me) stuff.
If I can start with  MartinLiss - you say "You didn't describe what you mean when you said it didn't work but is this what happens? You run it once with a clean sheet and it works, but then you try selecting another cell and when you run the macro all that seems to happen is that your name gets placed in the newly selected cell but no date or bolding?". Well, that IS what happens, except that it doesn't even run once any more. I follow the "Dummies" instructions to the letter, and I get exactly what you said - I select a cell (other than the ones with my name and the date in, obviously) - and all I get is my name in the newly selected cell, not bold, and with no date below it.
What do you mean by "That's happening because in the macro the cells for the date and the bolding were "hard coded" by the macro recorder so the same cells get the date and bolding every time the macro is run"? I don't really understand that.
I am sorry if I seem completely stupid, but I am new to this VBA, and I am sure I am not too stupid to follow the book - and it worked just the once but not since! I have erased all previous worksheets, and followed the instructions from "Dummies" time and time again, but still get the same result - just my name, not bold. There is no rush - this is a work related thing that I am trying to do, but I can take a bit of time to get my head round this particular problem, and then I can carry on plodding through the "Dummies" book so that I can do enough basic VBA to achieve my goal (which is not to write my name and date, of course, but to move on to useful stuff). I will also follow through carefully what everyone else has so helpfully suggested, but perhaps if MartinLiss, you could answer the above, that would be a most useful start.
0
Martin LissOlder than dirtCommented:
Let me ask if you've looked at what the macro recorder recorded. It's this.
Sub NameAndTime()
'
' NameAndTime Macro
' Meant to do stuff
'
' Keyboard Shortcut: Ctrl+Shift+N
'
    ActiveCell.FormulaR1C1 = "RichardCherry"
    Range("G14").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("G14").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("G14").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G13:G14").Select
    Range("G14").Activate
    Selection.Font.Bold = True
    With Selection.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
    Range("H13").Select
End Sub

Open in new window


Note that in lines 9, 11, 14 ,etcetera the recorder recorded the cell addresses from when you were running the recorder. So while line 8 says in effect "put 'RichardCherry' in the line you've selected" the other lines including the lines that bold the text refer to the originally selected lines.

If you want to watch the macro running, go to Visual Basic (you can select it from the Developer menu or click Alt+F11 and click in the left-hand margin of the first line of code. You can run the macro from there by clicking F5 and then click F8 repeatedly to see how the code flows. That's part of debugging and there's a lot more you can do. See my article on debugging. It says VB6 but it also applies for the most part to VBA.

If you have any more questions please feel free to ask.
0
Martin LissOlder than dirtCommented:
I forgot to mention that you'll probably find the code in a module called Module1 and you can look at the code in Module1 by double-clicking it. I also didn't mention that after clicking in the left-hand margin you should see a red dot and that dot is called a breakpoint. It's called that because the code breaks (pauses) when it reaches that line.
0
Martin LissOlder than dirtCommented:
In the "picture is worth a thousand words" mode here was I got after selecting A3 and typing Ctrl+Shift+N. Note that A3 gets your name while G14 gets the date and time because of lines 9 through 13 (some of which are unnecessary).

Run 1
I then selected A7, typed Ctrl+Shift+N, and got the following again because while the name goes into the seleted cell, the address for the date is hard-coded.
Run 2
0
FaustulusCommented:
@BlosMusic,
Did we all do permanent and irreversible damage to your enthusiasm to learn with our enthusiasm to teach?
In my estimation you should have reached the point of realising that writing your name in a worksheet cell can be achieved easier and faster without the use of VBA. This forum, and many others more or less like it, exists because there is no clear cut end to what you can learn about Excel and VBA. There being no end implies that there can't be a beginning, either. The next lesson to learn is that the usefulness of what you learn is determined by the usefulness of what learning lets you accomplish.
Pick a useful task and learn whatever you need to learn in order to accomplish what you set out to do.
0
BlosMusicAuthor Commented:
Hi,
No you didn't! I am grateful to you all, and am (when other work permits) working through your very helpful inputs. The reason for doing this writing my name stuff is simply that I like to understand why something goes wrong, and the "VBA for Dummies" book has done just that. I am a completely new learner, and want to do something other than writing my name (!), but I need an understanding of why something which worked once when i tried it, now longer does.
I shall adsorb what you have all said, digest it, move on and come back with more questions!
Thanks all once again.
Richard
0
Martin LissOlder than dirtCommented:
You may have more questions but you should open a new questions unless your questions are directly related to this one.
0
BlosMusicAuthor Commented:
Understood and will do.
0
Martin LissOlder than dirtCommented:
It looks like you've forgotten to assign points here.
0
BlosMusicAuthor Commented:
As the question seems to have been accepted by me, how do I assign points? I want to, but am not quite sure how! (been away for weeks and just catching up).
0
Martin LissOlder than dirtCommented:
You click one of these. And if you want to split points you choose the 'Accept Multiple Solutions' on on several posts.

Assign points
0
BlosMusicAuthor Commented:
Everybody was very helpful, and my problem was resolved - and I thought I had said this to all concerned.
I now want to ask a new question but cannot find my way round resolving the so-called 'abandoned question' so that I can ask a new question. Help!
0
Martin LissOlder than dirtCommented:
Do you see the these buttons on the answers to your question?
AcceptIf so then either click 'Accept as solution' on one of them if you want just one person to be awarded the points, or click the other one to split the points among 2 or more people.
0
rspahitzCommented:
I would split here:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28245949.html#a39511901
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28245949.html#a39512522

There's lots of good info, but these two focus on where to start and how to proceed before getting to the complex issues (which don't seem to work, possibly because the basics are not yet resolved.)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.