What’s My Type?


I am a big advocate of using the Enum construct in VBA, I feel that it is a simple way of adding a set of values, and provides a grouping, abstracting advantage that is not given by simple variables. Plus they can add to intellisense. As such, I should also be a fan of another specialised construct, the Type statement, but it is quite the opposite, I naturally avoid it. This is not because I think the Type statement is a bad concept, in fact I think the basic idea is great, but more because of its implementation; whenever I try to use it in anger, it breaks on me. As such, I tend to avoid it and create classes.


Earlier this week, it occurred to me that there was a case in which I could implement a Type, an elegant solution, with no chance of it breaking on me.


We all know that when your masterpiece sets application variables, such as calculation mode, in its processing, they should be reset at the end to whatever state the user originally had them. The normal way to do this is to save them in a bunch of variables at the start of the process, and restore them at the end.


My solution is very similar, but the benefits of Type add an elegance that I don’t feel the variables give it.


Here is that solution.


 


Public Type ApplicationValues


    ScreenUpdating As Boolean


    Calculation As XlCalculation


End Type


 


Private AppValues As ApplicationValues


 


Public Function ObfuscateData()


 


    With Application


   


        AppValues.ScreenUpdating = .ScreenUpdating


        AppValues.Calculation = .Calculation


       


        .ScreenUpdating = False


        .Calculation = xlCalculationManual


    End With


   


    On Error GoTo func_error


 


… main code


 


func_exit:


    With Application


       


        .ScreenUpdating = AppValues.ScreenUpdating


        .Calculation = AppValues.Calculation


    End With


 


func_error:


    ‘your error handler


    Resume func_exit


End Function


 


I think this is a neat little approach. Furthermore, it is simple and extendible, what more can we ask?


This may be obvious to you, it may be the sort of thing that you do all of the time, but it made me smile after I had implemented it. So much so that it is now a code snippet in my MZ-Tools, implementable at the drop (click) of a button.


 

4 thoughts on “What’s My Type?”

  1. Funny I’m the other way around. I don’t use emuns much, but I use Types quite a lot, almost like a simple class.

    I’ve used them for “none Boolean, Boolean’s” before and they work well, never thought of this though, good one!

  2. @Ross, that is why the Excel world is so varied and so interesting, as well as a great, flexible product, we all have our favourite ways of using it .

    You should use enums though, they are so useful. I think I might do a blog on them.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>