EXCEL VBA TO CALL A SUBROUTINE HOW TO ASSIGN OR

Excel Vba To Call A Subroutine How To Assign Or

Upon completion of the task, It returns control to the calling code, but it does not return a value. A Subroutine begins with a Sub statement and ends with an End Sub statement. The program structure of a sub procedure is as follows:In the previous article, you learnt how to assign or modify a shortcut key using excel option. In this article you will learn how to assign shortcut keys to your already written procedure or subroutine using VBA (excel macro).

This article I am posting for you to know that вЂњYes it is possible using excel VBA code as well.вЂќNow, when you stop the recording, save the macro, and run it, Excel simply. Every subroutine (also called Procedure) in VBA starts with Sub and ends with.In Excel, VBA code can be stored in three different locations: in a Visual Basic module, in a Visual Basic class module, and 'behind' worksheets and workbooks. To edit code 'behind' a worksheet or a workbook: Activate the Visual Basic Editor (press ALT+F11). In the Project Explorer window, you will see entries similar to the following:A vba procedure is usually of two types, a sub procedure or a function. Calling a procedure refers to using or executing a procedure. Procedures should reside in their appropriate modules.

One is using Application.OnKey and other one is using Application.MacroOptions. Here in this article we are going to learn both the techniques.Method 1 : Assign a Shortcut Key using OnKey in Excel VBAOnKey is an Application method which makes a particular procedure run in excel vba when a specific key or combination is pressed.Key as String: is the Key combination you want to assign to your procedure.Procedure: is the name of the procedure which you want to be run.To assign a Shortcut Key CTRL + b for a Subroutine or procedure named MyProgram then execute the below code and you are doneвЂ¦As soon as you execute the above statement, Shortcut Key CTRL + b will be assigned to the macro named вЂњMyProgramвЂќ and pressing the key combination CTRL + b will trigger the procedure MyProgramIn the above example you can see I have used Caret Sign (^) before the letter b. Caret Sign is used for CTRL Key. Similarly percentage sign % is used for ALT Key and Plus Sign + is used for Shift Key.Method 2 : Assign a Shortcut Key Application.MacroOptionsApplication.MacroOptions Macro:=вЂќвЂќ, Description:=вЂќвЂќ, HasShortcutKey:=True, ShortcutKey:=вЂќвЂќApplication.MacroOptions macro:='MyProgram', Description:='Description of the Macro', _In the above example you can input the description of the Shortcut Key as well.Advantage of Using VBA Code for assigning a Shortcut KeyAs you have seen in the previous article to assign Shortcut key in excel options, there you can assign only a key which can be typed in the Option box.

The VBAEditing window shows empty, or as below with a module open from FUNCRES.XLAM. Alternatively,You can go to the вЂњDeveloperвЂќ ribbon and choose вЂњVisual BasicвЂќ. Common errors when compiling or executingOpen a new Excel workbook and access the VBA editing window by pressing Alt+F11 on a Windows computer or Option+F11 on Mac. In this chapter we will discuss the following Excel VBA elements: It is useful, but not indispensible to first have goneThrough Chapter 1: How to record a macro.

This is good programming practice as it prevents possible mistakes in your macros, for example if you inadvertentyMisspell the name of a variable (more about variables later in this chapter).If Option Explicit doesn't show up automatically at the top of your new module then you can type it yourself on the first line of the empty sheet. Figure 1: VBA editing window on Mac in Office 365Option Explicit is a module-level statement, which means it holds for all the programming you do in this module.Writing Option Explicit at the top of a module means you have to define (вЂњdeclareвЂќ) all variables usedIn the module. If you have any issue accessing the VBA editing window then have a look at the Preparations sectionIn the Introduction.

The termвЂњreserved keywordвЂќ means that Option Explicit is something that has a predefined function in Excel VBA and can onlyBe used for the predefined purpose. Also the first letterOf each reserved keyword should automatically turn to a capital letter if you initially typed all in lower case letters. All reserved keywords recognised by the editor turn blue.

The type of code that executes an action is a вЂњSubprocedureвЂќ, whileA вЂњFunctionвЂќ returns a value (like the functions in the Excel worksheet). Generally the objective of a group of code isEither to execute an action or to return a value. -> EditorвЂќ) andIn the module you have to write the programming within different groups of code. Or, if you didnвЂ™t want to use a reserved keyword, it indicates you would need to use other words.If you want Option Explicit automatically in your newly created modules (recommended), then go in the VBA editor toвЂњTools -> Options -> EditorвЂќ (on Mac: вЂњExcel -> Preferences.

The result on your screen should be theThe VBA editor immediately created the вЂњEnd SubвЂќ line when you hit enter. In this case type вЂњsub WrittenMacroвЂќ and hit enter. How to use functions is discussed in Chapter 7.To start a new subprocedure type вЂњsubвЂќ (anywhere below Option Explicit) followed by a space and the name of theSubprocedure.

Copy & PasteLetвЂ™s write in this subprocedure the same action that we recorded in Chapter 1.What we want to do is that the value from cell B3 is copied to the range B6:F14. In contrast to 'End Sub' appearing automatically here, in subsequentChapters you will see that for most statements in Excel VBA you not only need to write where it starts but also where it ends. Imagine you would have several subprocedures in your VBA Project then itEnables you to execute only a specific part of your code.

While you are in the VBA editor and within the lines of the subprocedure the sub will run if you press the F5 key. For example write вЂњGood morningвЂќ. Whatever is on the right-hand side will beAssigned to the contents of what is on the left-hand side.To test if it works we need to write something in cell B3.

This happens often if you have several workbooks open and did notDefine in which one the range is located that is being referred in the VBA code (like we do in this tutorial). If the message 'Run-time error '1004', Message 'Range of object '_Global' failed' shows up it is because the VBA codeExecution cannot find the object you are referring to. Once corrected you can rerun the code as described above.If you were to leave debugging before re-executing the code you can click the 'Reset' button, the square button in the When this occurs, press 'OK'In the error notification window, identify the issue and correct the code. This respectively means thatSomething was not properly defined or an unexpected event happened while executing the code. An error message comes up with 'Compile Error' or 'Run-time Error'.

In case you have made a logical error in the VBA code, particularly while creating repeatStatements as described in Chapter 4, this is often the result. The code is executing inifinitely. Apart from changing your code and making it more explicit where the referred range is to beFound, the quick solution is to ensure you are in the correct workbook (and in this case even the correct worksheet) whenExecuting the code in the Macro window or before switching to the VBA editing window.

Whereas вЂњRecordedMacroвЂќ literally states copy & paste lines, in вЂњWrittenMacroвЂќ we have just assigned the value of B3 to B6:F14. The two codes are: Figure 5: Comparison of recorded and written VBA code on Windows Office 2013.Whereas the вЂњRecordedmacroвЂќ includes each and every action we performed manually for the copy pasting, we tried to write as little as possible in вЂњWrittenMacroвЂќ. It is instructive to see the difference between theRecorded macro and the written macro. We called it вЂњRecordedMacroвЂќ.

Inside the drawer is a note with one particular greeting, e.g. For example, in my desk I have a drawer with вЂњGreetingвЂќ on it. You could think of it as a drawer that has a name on it. A variable is a named storage location that has one particular value.

The type determines what kind of value it represents e.g.: an integer or decimal number, a word of TRUE/FALSE (Boolean). Therefore we first need to determine what type of variable it is. We will first put the value of B3 in a variable before assigning it to the range. The variable (the drawer) remains the same and can be reused endlessly while we can keep changing its contents.In our example we can use a variable as an intermediate step when copying the value of cell B3 to B6:F14.

Dim: can be used to declare a variable in a subprocedure or at the module level. There are three statements that can be used to declare a variable: Declaring a variableOnce we know the type of variable we want to create we can define the variable, called вЂњdeclaringвЂќ in Visual Basic. Use the Variant data type therefore as little as possible. Always try to use data types thatDefine your variables as narrowly as possible so you will have fewer problems to detect errors in your programs and your programsWill run faster if you use data types that use less storage. If you do not assign a data type when declaring a variable it will automatically become a Variant.If you look for вЂњData Type SummaryвЂќ in the VBA Help you will find a complete list of available data types (to accessThe help, press F1 while in the VBA editing window or click the question mark symbol in the menu).

Excel For Mac Vba To Call A Subroutine