Use a VBA Macro inside an Excel Cell

This post will explain to all the Excel fans out there (not the big shot developers, just the sophisticated users among us) how to create a simple macro within Microsoft Excel, and then how to use that macro to calculate a single cell value.

Ok, Let's improve our worksheets with the VBA excel macros:

(Don’t forget to enable Macros now)

  • Open the VBA Editor (Alt-F11).

  • Add a new module (or use your existing module)



excel vba interface - add module to project


Our macro will encrypt a text (very simple - just for the example, don't use it for your applications, click here for advanced encryption methods).

  • Let’s add the next function to the code:

Function enconeway(ByVal TextToEncrypt As String) As String
Dim tmp As String
Dim cnt As Integer
For cnt = 0 To Len(TextToEncrypt)
tmp = tmp & Asc(Mid(TextToEncrypt, cnt, 1))
Next
tmp = StrReverse(tmp)
enconeway = tmp
End Function

  • Now let’s use it:


vba cells - macro usage


  • Create a word list, and near that list just edit the first cell as seen at the picture above.

Vibe Cellular.com Red 200x200


This is the outcome:

vba worksheet with all the cells using the macro

A short Video to show the process visually:



I've found a great site with alot of screen shots and examples (mainly about how to use the VBA environment), there is also a complete course you can take there, so go ahead and take a look.

That’s it folks, very easy and very fast, now go ahead and kick some macros…

Udi.
Oh: don’t forget to save you’re excel as a macro enabled workbook.
excel macro enabled worksheet



If you've never owned an Excel package, maybe now is a good time to purchase one..

Microsoft Store

1 comment:

  1. Excellent and very simple explanation.
    From no background, I became able to do VB scripting within Excel in 5 minutes.
    Many thanks...

    ReplyDelete