Power Apps - VBA Subs and Functions

#powerapps #powerplatform #lowcode #vba

Power FX the LowCode language of the Power Platform is often sited as an expression based language like Excel, and I definitly agree. But just like Excel expressions have limitations so does Power FX, most notably code reusability. Excel got around this with VBA and custom code blocks called Sub's and Function's.

Subs are described as:

A sub can be described as a small program within the VBA Editor that performs a specific action in Excel. It is used to break large pieces of code into smaller parts that can be easily managed.

and Functions:

A VBA function is similar to a sub procedure, only that the former can return a value whereas the latter cannot. It is a piece of code that can be called anywhere in the VBA Editor and eliminates the need to write the same lines of code every time. VBA allows users to use built-in functions, as well as user-defined functions.

So the main difference is a sub runs and ends, where as a Function runs and returns a value.

Luckily just like with Excel and VBA Power Apps has its own versions of Subs and Functions.

User Defined Function

User Defined Functions are hot off the press and still in preview. Anything in preview is kind of a "use at your own risk". They are generally very stable (Microsoft are our friends remember 😉) but they are subject to change, might get discontinued, and not supported (so don't think you can raise a bug and get it fixed quickly).
To use them you need to first activate "New Analysis Engine" and then "User Defined functions" in Settings/Upcoming Features.

Next we head over the formula parameter from the app section

The user defined function works like this

functionName( inputName:inputType ):OutputType (
Your function
)

Any value calculated in the function is automatically returned, e.g



AddOne(num:Number):Number=(
    num+1
)

Not particularly useful, so lets do a more useful example.

The Char() function converts a number to its ASCI character, so Char(89) returns "b". But there is no reverse way, to convert "b" to 89, so lets make a user function to do it.
Basically what the function does is create an collection from 1-255 and populate the character it represents. Then lookup up that collection to find the number from the character:



ASCI(inputString:Text):Text=(
    If(Len(inputString)>1,
        "Only single characters accpeted"
    ,
        LookUp(
        ForAll(Sequence(255), 
                {Num:Value, Character:Char(Value)}
        ),
        Character=inputString
        ).Num        
    )
);

To then get the value I pass in the input from a text input.



ASCI(inCharacter.Text)

Additionally to stop it returning blank if more then one character is submtted I add a logic to return a error message.

Now according to the documentation you can not pass tables/record in and out of the function, but you kind of can.

By using the old stringfy/json approach we can convert our input to a string and then convert our output from a string back to table/record.

So lets take our ASCI example but change it from one character to a whole word and we want every character converted and returned as a collection.

The input collection (the word) is already a string so that makes that bit easier, the complicated bit is processing the collection. We need to split the word back into a collection, loop over it to find the ASCI code and then change it back to a string to pass out.



ASCIcollection(inputString:Text):Text=(
    Concat(
        ForAll(Split(inputString,""),
            LookUp(
                ForAll(Sequence(255), 
                        {Num:Value, Character:Char(Value)}
                ),
                Character=Value
            ).Num   
        )
    ,
        Value&"-"
    )
);

The gallery just then splits the return string by the "-" delimita.

Important call out, User Defined Functions are scoped entirely to the function, so they can't impact outside of it. That means no setting variables, collections, navigation, etc. Though you can read in external variables and collections.

And that's a User Defined Function, as I said its still new so excited to see what updates it gets.

Select

Unlike Functions there is not (currently) a direct copy of a Sub in Power Apps, but we do have the Select() function that we can use. This imitates a button press, so we can trigger that buttons OnSelect code.

To set one up simply add a button and set its visibility to false (the biggest negative of this work around is it is scoped to the screen, so a button will be needed on every screen, another reason to go single screen instead of multi).

Then we add our code to the OnSelect, the code is not scoped so can do anything and reference any variable, collection or component.

For this example Im going to add all of my input resets, you could imagine this being used for a 'Save Complete', 'Save Draft' and a 'Reset' Buttons.



Reset(inAddOne);
Reset(inCharacter);
Reset(inCharacters)

Then all of the other buttons Select that button:



Select(buSubReset)

I used the buSub as my naming reference to identify sub buttons

And that's it, their key powers are being the single version of truth, and reusability. That means that you only update one section of code for changes, and there is less code throughout your app. So overall your app will in theory have less bugs, be more readable, and have improved performance.