Hello all and welcome back to the Excel Tip of the Week! In this week's post, I am continuing my new occasional series documenting my own experience of learning VBA. This post is the second in that series, which began with TOTW #141 on making a Stamp Duty Land Tax function. The example problem This time, we are going to make a more abstract function. This one will help us in doing a bit of mathematics - taking a number and finding its first factor (or returning "Prime" if it is prime). It's not an essential function, but the process we're going to use to build it is very instructive for some vital VBA techniques. Essentially, there is no particularly more efficient method for finding factors of a number than just trial and error. This is actually how almost all computer security works (but we won't go into the details here). So we will need to make a trial-and-error process - and define how the process will stop. Let's start with what we established last time as the baseline parts of our function: Public Function Factor(N As Long) As Variant [function goes here] End Function We will need a couple of additional variables here - one for the candidate number we will use as a divisor, and one for the limit of when to stop trying (i.e. when the number has been shown to be a prime): Dim Divisor As Long Dim Limit As Long Limit = N ^ (1 / 2) Divisor = 2 The Dim we have chosen for our variables and function here: N (the input) is Long - a number with a lot of memory space, so it can be large Factor (the function itself) is Variant - because it might be a number or text Divisor and Limit are also both Long We can change a variable's value by using an = sign. For example, we write: X = 5 ...to make the value of the variable X equal to 5. Hence here we have set the starting value for the Divisor 2, and set the limit at the square root of the number N. If no number divides N by the time we have reached the square root, then no number ever will.
Building a loop We need to create a simple process that will be repeated until our Limit is reached, or our answer is found. We do this with the Do While... Loop condition: Do While Divisor <= Limit [process goes here] Loop So here, we have set a condition for the code to check each time the loop is iterated - that the divisor has not yet exceeded the limit. The code inside the middle will be done over and over. The actual code in the middle needs to check if the divisor we currently have divides in to N, and if so, we have found our factor and we can stop running the function and return that as the answer. If not, we need to move on to the next divisor. In VBA, this looks like: Do While Divisor <= Limit If N Mod Divisor = 0 Then Factor = Divisor Exit Function Else Divisor = Divisor + 1 End If Loop The Mod operator here will tell us the remainder of N / Divisor - so a result of 0 tells us that the division is perfect. Note also the use of the 'Exit Function' command - this is how we make the code stop prematurely if a factor is found. Finally, we need to set what the code will do if the loop exhausts all possible candidates - which is to set the output to be 'Prime'. The entire function looks like this: Public Function Factor(N As Long) As Variant Dim Divisor As Long Dim Limit As Long Limit = N ^ (1 / 2) Divisor = 2 Do While Divisor <= Limit If N Mod Divisor = 0 Then Factor = Divisor Exit Function Else Divisor = Divisor + 1 End If Loop Factor = "Prime" End Function
Now, there is room for improvement here - the function doesn't work properly when fed a non-natural number, for example - but the basics are all there! Best of luck with building your own loops and VBA! Previous post - Conditional Formatting: Colouring a whole row TOTW index Next post - RANK.AVG, RANK.EQ This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.