Quadra

Connecting Technology and Business.

Excel Macros - how to build, run and follow best practices...

Excel macro best practices 

  • It's usually a good idea to turn on the Use Relative References option because the Macro Recorder will be more likely to produce the steps that you expect. Turn on this option by clicking the Use Relative References button in the Code group on the Developer tab. Learn how to show the Developer tab (by default, it's not shown) in the "Record a macro" section, below.
  • Inspect your macro in the Visual Basic Editor after you've recorded it to see if you can improve it or at least understand how it works by looking at its instructions. Do this by selecting your macro in the Macros dialog box (in the Code group on the Developer tab, click Macros) and then clicking the Edit button.
  • Make a habit of saving your macros to the personal workbook so that you can reuse them on your computer. Do this by selecting the Personal Macro Workbook option in the Store macro in list when you record a macro.

Record a macro

First, make sure the Developer tab is visible, because that's where all the macro commands are found:
  1. Click the File tab, click Options, and then click Customize Ribbon.
  2. Under Main Tabs, make sure the Developer check box is selected.
Then, start the Macro Recorder:
  1. On the Developer tab, in the Code group, click Record Macro.
  2. Optionally, you can assign your macro a shortcut key combination so that it's easy to run.
  3. Click OK to start the Macro Recorder.
  4. In your workbook, perform the actions that you want recorded, which can include typing words or numbers, clicking cells, clicking buttons, dragging cells, formatting, and more.
  5. When you're done with the actions that you want recorded, click Stop Recording.

Run your macro

  1. On the Developer tab, in the Code group, click Macros.
  2. In the Macros dialog box, find your macro and click Run.
    Note If you assigned your macro a keyboard combination (for example, CTRL+SHIFT+M) when you started the macro recorder, you can use that shortcut to run the macro.

Record a macro in your personal macro workbook

By saving a macro to the personal macro workbook, you make your macro available every time you open or create an Excel workbook on your computer. It's best to make this decision when you first record your macro, because it requires a bit of work (and knowledge of the Visual Basic Editor environment) to later move a macro to the personal macro workbook.
  1. Click Record Macro and then, in the Store In list, select Personal Macro Workbook.
  2. Click OK to record your macro, and click Stop Recording when you're done.
If you have already created a macro in your current workbook and would like to copy it to your personal workbook, you can record an empty macro whose instructions you can then replace. Do the following:
  1. On the Developer tab, in the Code group, click Record Macro.
  2. In the Store In list, select Personal Macro Workbook.
  3. Give the macro the same name as the macro that you've already saved to your current workbook (that is, the macro whose instructions you want to copy).
  4. Click OK to start the Macro Recorder, and then immediately stop the recording.
  5. On the Developer tab, in the Code group, click Macros, select the macro in the current workbook that contains the instructions you want to copy, and then click Edit.
  6. In the Visual Basic Editor, select the entire macro, starting with "Sub" and ending with "End Sub."
  7. Click CTRL+C to copy the macro, and then close the Visual Basic Editor.
  8. Click Macros again and this time select the macro you just recorded to your personal macro workbook (in the list, it's probably preceded by PERSONAL.XLSB!).
  9. Click Edit to start the Visual Basic Editor, and select all the lines of the macro, starting with "Sub" and ending with "End Sub."
  10. Paste the macro that you previously copied over these instructions to replace them.
  11. Close the Visual Basic Editor to save your changes.
When you close Excel after you've saved a macro to your personal macro workbook, you'll be prompted to save the changes to the personal workbook. Be sure to click Yes to save those changes.

Virtualization towards a Private Cloud

​Maintain Control and Governance with Private Cloud Computing

Faster provisioning is great but not if it means compromising security or unauthorized access to resources. Many surveys on cloud computing cite security as the most significant concern hindering the adoption of cloud computing. A host-based distributed architecture enables virtual networks to be protected at the host level, leading to lower overall costs and higher quality of service.

Automated provisioning also requires control to ensure compliance with appropriate access policies. One approach is to control the consumption of resources by users before the resources are provisioned either through request/approval workflow or quotes/limits on what can be provisioned. An alternative approach is to freely allow end users to consume resources from the infrastructure but observe this consumption to generate usage reports. Those usage reports can then have cost models applied to them to generate cost reports that display the cost of the consumption.
 
Obtain Agility with Policy-Driven Automation
 
Resource management policies can improve utilization of shared infrastructure by allowing resources to be overcommitted beyond baseline reservations. Multi-cluster linked clone technology will enable identical virtual machine configurations to be provisioned rapidly and without a full duplication of the original template. Resource pooling and virtual distributed network configuration will reduce the amount of hardware needed to deliver services, and will enable intelligent policy management mechanisms like distributed resource scheduling. Software controls can enforce isolation that minimizes the risk of a user-driven or system-driven fault.
 
With this unique policy-driven automation in place, one will be able to essentially deliver zero-touch infrastructure, where IT is transformed into a service provider defining and delivering services rather than operationally responding to custom requests.

Switching between worksheets

Toggling between multiple worksheets in a notebook you can always use your mouse or even the keyboard shortcuts. (For example, CTRL+PageUp activates the previous sheet in your workbook, while CTRL+PageDown activates the next one.) 

But what if you aren't sure of the one you want to look at? What if you want to see a list of all the worksheets in that particular workbook? It's simple: right-click the tab navigation buttons
Tab navigation 
 
... and a floating list of all the worksheets in the workbook will appear, as pictured below. Just click the one you want. (This is especially useful when sheet names are long.)

 

The names of all my worksheets 

Downsides to Virtualization

First, increased network complexityaffects performance. Aside from merely increasing the number of network devices, virtualizationadds tiers to the switching fabric, increasing latency, power consumption and managementcomplexity.

Second, the consolidation of virtual machines on physical servers affects switching scalabilityand performance. As dual-processor servers with six-, eight- and even 10-core CPUs becomecommon, consolidation ratios will climb. Currently, a hypervisor virtual switch with a workloadof 10 to 15 VMs per system extracts a modest overhead of about 10% to 15%, but thatfigure that will undoubtedly increase when handling scores of VMs.

The third challenge is that software switching complicates management and security. Network monitoring, management, traffic reporting and security tools use standard protocols operating on physical ports, but as more traffic is switched within the hypervisor, these tools lose visibility into a significant amount of network activity. Some vendors make their monitoringand analysis software available on VMs to regain visibility, but these are proprietary solutions that typically support only one or two hypervisor vendors, and usually come with additional license costs.

Fourth, the ability to seamlessly and transparently move VMs from one physical server to another complicates management and security. Such dynamic movement of application workloads becomes a headache when keeping network policies aligned with applications.

Fifth, virtualization exacerbates demands for shared storage, due to the inherent need to decouple OS images, applications and data from the underlying server hardware.

-  Analyt i c s . Informat ionWeek. com