Tag: Technology

  • Building a Versatile Variables Container in Excel

    Building a Versatile Variables Container in Excel

    Microsoft 365 (and specifically Microsoft Excel) is my favourite tool when it comes to work, and also really fun to play with in my spare time as a ‘hobby’ that also helps me improve my skills.

    I’ve used probably dozens of different methods of storing ‘sets’ of variables for the tools and mini programs I’ve created in Excel over the years, some very rudimentary, and others more involved but also difficult to maintain or fragile in various ways.

    However, recently while playing around with Power Query and Excel formulas (yes, I do this when I let my mind wander), I came across an idea for a robust variables container stored in an Excel table that can be accessed in several different ways throughout a given tool. You can access or refer to these variables using good old Excel functions (with a LAMBDA, or a UDF if you’re old school), with a simple Power Query function call, or a few lines of VBA in your editor (see the UDF), depending on the needs of your project.

    Getting Started

    Let’s go through the basics of the project’s needs first:

    First, create a basic table called Variables with a set of Name-Value pairs.

    This table will contain all the variables we want to reuse across different places in the tool or want to be user-configurable later on.

    1. The LAMBDA function

    First, we can create the LAMBDA function (if you use Excel 365, which I highly recommend!), by putting the following function in the Name Manager (I used the name getVariable for these functions, and variableName for the name of the variable):

    =LAMBDA(variableName, XLOOKUP(variableName, Variables[Name], Variables[Value], "No matching variable.", 0))

    Using this function elsewhere in the workbook, we get, just like we want:

    2. The Power Query function

    Next up, we will create a Power Query function to be able to refer to a variable from this table and get back the value for it.

    Launching the Power Query Editor (from the Data tab), I will create a function called getVariable (again), and include the following:

    (variableName) =>
    let
        Source = Excel.CurrentWorkbook(){[Name="Variables"]}[Content],
        #"Transposed Table" = Table.Transpose(Source),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        Result = Record.Field(#"Promoted Headers"{0}, variableName)
    in
        Result

    Note that I’m still using variableName for the variable here, and this allows you to invoke the function and get the expected result back if we want to refer to this variable in another function:

    3. The VBA function

    Finally, we can create a quick function in VBA that allows you to use these same variables across your project, making it fast and easy to maintain your code however you have it set up.

    Open the VBA editor, and in a new (or existing) module, I’ll write the getVariable (again!) function:

    Function getVariable(variableName As String)
    
    Dim VariablesTable As ListObject
    
    Set VariablesTable = Main.ListObjects("Variables")
    
    getVariable = WorksheetFunction.XLookup(variableName, VariablesTable.ListColumns(1).DataBodyRange,  VariablesTable.ListColumns(2).DataBodyRange, "No matching variable.", 0)
    
    End Function

    With this function, you can easily get any variable from your table anytime you need it, and you’ll be able to change the parameters in your code without digging in to the developer tools.

    Well, that’s it. With this simple, basic Excel table and three short code snippets, you’re ready to tackle nearly any Excel project imaginable while keeping your data in check and making sure you don’t leave hard-coded values anywhere in your Excel tools.

  • COVID Alert is now publicly available! [Links]

    You can download the COVID Alert exposure notification app in Canada now! It’s available on the iOS App Store as well as the Google Play Store.

    See more details of how it works and what it looks like in my older post when I started using the beta:

  • Here’s What Canada’s ‘COVID Alert’ app (beta) Looks Like

    Here’s What Canada’s ‘COVID Alert’ app (beta) Looks Like

    [UPDATE – July 31]: The app is now publicly available. Go get it!

    On Wednesday, Health Canada finally released a beta (test) version of the Canadian ‘COVID Alert’ exposure notification app.

    I have been waiting for a couple of months to finally be able to get this set up once Apple and Google announced they would be building exposure notifications in to their operating systems, and it’s finally here (if you are willing to jump through some hoops and help test it out).

    Let’s take a look!

    When you first launch the app, you’re given a bunch of info right off the top. You’ll also have to accept exchanging exposure tokens with other phones, as well as receipt of exposure notifications, and then standard iOS app notifications as well. Here’s what the setup screens look like:

    Once you’ve got everything activated, there’s not a whole lot to do besides look at the menus and edit your information.

    I was informed via email they will be testing what an ‘exposure’ looks like in the app over the next 2-4 days, so if that happens, I will definitely share that information!

    There’s not much to the app to be honest (which is a good thing), but there is a little more information to go over.

    In the meantime, here’s some information provided under ‘How it works’:

    These exposure notification apps (one per country, by rule) are meant to be for public health reasons only, so they’re very minimal, and outside of use in conjunction with your doctor, you won’t really notice it on your phone.

    The only information the app collects and is able to use is a set of randomly generated ‘codes’ from the phones of others you interact with, and your phone sends out a similar set of ‘codes’ to people around you. No private information is stored or sent anywhere, like location or your personal details.

    If you test positive, your doctor will give you a one-time use code which you enter in to the app, and this (if you choose to do it) will notify anybody else who has the app that you were nearby them and may have exposed them to the virus, because their phone has stored your ‘code’ from while you were near each other.

    It’s a pretty incredible, secure system, and I’m really eager to have it see wide use in Canada and around the world, on Android and on iOS, over the coming months and possibly years.

    When I hear more about the public release, I will post updates here!

  • FaceTime Isn’t Broken

    FaceTime Isn’t Broken

    Update: Update is out. iOS 12.1.4 addresses this bug as well as another security issue that Apple found while auditing the code for FaceTime.

    On Monday night (January 28), talk of a serious Group FaceTime bug hit the internet in a big way.

    New in iOS 12: Group FaceTime

    If a would-be attacker used a specific set of steps that were not typical for a regular FaceTime call, they could activate the call recipient’s microphone on their iPhone (or, presumably, iPad) without them answering the call. There was an extra privacy concern that if the recipient of the call declined the request, their camera was mistakenly activated as well, even if the phone looked like it was asleep.

    There is no indication this bug was exploited maliciously, and it appears to have been reported to Apple at least a week and a half before the explosion of attention on January 28. Moving quickly once this story went public, Apple shut off Group FaceTime (a new feature that was introduced with iOS 12 this fall), effectively blocking this exploit from being used. In all, the bug was active for about 2-3 hours with a large audience, as Apple presumably scrambled to find a way to quickly fix it.

    Immediately, Apple put out a press release saying that a permanent fix for this bug would be coming later this week, and shutting off Group FaceTime has mitigated the problems associated with the bug until the fix is released.

    Unfortunately, because the news is effectively entertainment now, the following evening (Tuesday), local news, all the way up to late-night comedy shows, all talked breathlessly about the story, and at least from what I heard, none mentioned that the offending problem has been completely disabled until a proper fix is in place. In other words, the window when anybody at any scale could have been harmed by this was exceptionally small, only a few hours at most.

    Now, though, the viral story of ‘Turn Off FaceTime’ will circulate for years, even though in my opinion it’s probably one of the very best ways for a group of Apple device users to communicate with audio/video, and even when the feature is fixed, there will be no news stories saying ‘You Can Turn FaceTime Back On Now’, even though after Monday evening, there was no need to turn it off.

    There are a few big lessons I take away from this:

    1. Basically every news story is as well-researched as the one you know the intimate details about beforehand (not at all well-researched). Take them with a grain of salt.
    2. Every piece of software has bugs and flaws at some point in its development cycle. Obviously, big flashy bugs like this are a BIG deal, but it’s a reality of software that they will come up. The best thing you can do as a developer is to put systems in place to be able to deal with them quickly, and in my opinion, Apple’s ability to pull the plug on Group FaceTime without taking the entire system down is an example of good design.
    3. Don’t take your privacy for granted. People are going to see this story and turn off FaceTime because this was a huge privacy issue. However, I promise you that there are much bigger and more severe privacy violations going on at huge companies around the world right now, and because it is status quo, we all kind of just give them a pass. You should ‘audit’ the programs you use from time to time, and if you’re able, do some research on the privacy over-reaches of companies like Facebook. You’d be surprised the kinds of things they are caught doing on an ongoing basis, but it’s not a news story for some reason.

    So, I didn’t turn FaceTime off, and unless something changes, I don’t think you need to either (if you didn’t already). If you’re paranoid about being watched/heard in your home, FaceTime is far from your biggest concern (this bug is no longer a risk as it stands today).

    Humans are flawed, so it stands to reason that the software we create isn’t always perfect either. But writing off technology because of one viral news story is harmful to all of us, because the news can’t, and doesn’t, cover everything.

    Please, don’t turn off FaceTime and vow never to trust it again because of this story. Your privacy is, and always will be, at risk, but that doesn’t make this particular piece of software the problem.

  • Stuck in the Past: Projection Woes

    Next week, I will be heading to Montreal, where I’ll be giving three different presentations to three different audiences in three different rooms. I’ll be bringing my laptop, my iPad, and my phone with me, any of which has the built-in capability to show a PowerPoint presentation. I’ve given these kinds of presentations before, and I’m not particularly nervous about the content of the talks.

    However, there is something about this weekend that is causing me serious bouts of anxiety, and that’s showing the actual presentation. Like I said, I’ll be bringing 3 different computers to the conference, which connect to other display devices via Lightning adapter (iPhone and iPad) or mini DisplayPort (my MacBook Air) to DVI or VGA or even HDMI, or via screen sharing if there was an Apple TV/Chromecast(?) involved.

    However, what I *don’t* know is what display technology will be available on the other end, connected to the projector. I am aware that many universities are starting to make sure projectors have connection options for Mac, which means one or more of these options may just be ready and waiting for me. But since I want to actually know at least one of these options WILL be available, does that mean I need to go and buy at least one adapter for VGA/DVI/HDMI just in case any of those is all the projector works with? Should I just buy an Apple TV for the weekend, hook it up, and share my screen to it (again, hoping the projector has an HDMI hookup). The Apple TV method means I’ll also need access to a stable Wi-Fi connection to run the screen share, which isn’t always the case.

    I’m very risk averse, but I also like to be prepared for any possibility when it comes to this kind of thing, but it feels like there should be a better way when it comes to giving presentations in an unfamiliar environment. Conferences are a VERY common thing, and it just seems like there’s no good way to do things consistently with so many moving parts.

    Side note: don’t even get me started on using a secondary device as a remote to control the presentation. This technology has existed for a decade, but the only software integration that currently exists for PowerPoint is that a presentation on the iPhone can be controlled via the Apple Watch. It just feels like these kinds of things should be further along than they are.

  • The end of green screen, and possibly the beginning of true 3D movies

    Since Lytro’s tech basically captures all the 3D information in a scene, the imagery is unusually friendly to CGI. Placing virtual objects at exactly the right depth in a scene is essentially taking advantage of a native ability of the footage.

    This is possibly the beginning of what I’ve been looking for since the re-renaissance of 3D movies in the last 10-15 years. As the new ‘real’ 3D movies came to theatres, what I really wanted was to be able to view a scene in full 3D (ie. with the right visuals, everything could theoretically be in focus at all times) and to be able to focus on whatever I wanted at any time.

    You have no idea how disorienting it is (unless you do this, like I do) to be engrossed in a 3D movie, just to glance at something in the background of a scene and see that you can’t focus on it. Is that what living with glasses is like? It’s terrible.

    I’m really hopeful that advances in 3D movies along with these Lytro cameras that are capable of capturing all aspects of a 3D scene could mean an overhaul of not only visual effects, but of the 3D movie as a whole. I think if we were to see something like this, we would be able to truly revolutionize cinema. It might take a VR type experience to truly make this happen, but I think at a certain point that’s where we’ll end up, and I can’t wait to focus on the meaningless background part of a scene in a movie, just because I can.

    > Lytro’s new Cinema camera could mean the end of green screen

  • A list of the apps on my phone that can make calls

    Doesn’t require phone number:

    • FaceTime
    • Phone
    • Contacts
    • Facebook Messenger
    • Snapchat
    • Google Hangouts
    • Messages
    • Whatsapp
    Can/does use your phone number:
    • Phone
    • FaceTime
    • Messages
    • Chrome
    • Safari
    • Mail
    • Notes
    Announced, but hasn’t shown up yet:
    • Slack
    As it turns out, pretty much every remotely social company has a way that people can talk to one another in a phone call-type manner. Many of these apps also let you use video chat, but people have no idea. For instance, you’ve been able to make phone calls (and recently, video chats) with any of your Facebook contacts on your phone, for such a long time. But I can routinely blow people’s minds by telling them that, because approximately nobody* knows about this feature.
    Snapchat updated their app yesterday to revamp chat, and added the ability to send video clips or make voice calls to any of your Snapchat contacts who’ve added you back. But none of the features in the update are actually new capabilities your phone didn’t have before, and I’m betting people aren’t going to be making use of this feature any more than they did, no matter how good it is. 
    If I were a gambling man, I’d put money on Snapchat continuing to grow at a rapid pace for quite some time. But people who already have a predefined way of communicating, like my generation and those older than me, won’t use Snapchat for voice calls because to us, the way you make a phone call is by calling a phone number.
    But the kids, they don’t obey these rules. They do whatever their friends are doing, and their friends don’t make phone calls to a phone number. That’s not cool anymore, at least not until their parents stop doing it.
  • Podcasts Like ‘Serial’ Are Encouraging Literacy

    [L]istening, unlike looking at a written page, is more active, since the brain has to process the information at the pace it is played.” My student Roberto offered similar insight: “I think it helps me out with my reading since I have to keep a pace up.”

    Huh, turns out the best ways for kids to learn aren’t determined by a group of adults telling them what’s best, kids (like everybody) are going to learn best when given options and a choice. And just like I always say, podcasts are a great way to learn and take in new information, and listening isn’t nearly as much work as reading.

    And if learning is work, you’re much less likely to want to keep doing it.

    > Why Podcasts Like ‘Serial’ Are Helping English Teachers Encourage Literacy – The Atlantic

  • Ed Snowden can say whatever he wants

    “The FBI says Apple has the ‘exclusive technical means’ to unlock the phone,” Snowden said. “Respectfully, that’s bullshit.”

    It must be really nice to be in a position where you can say something like this with confidence, having not a care in the world about repercussions. Not saying Snowden is in an enviable position overall, but he can say what he really thinks even better than the current American Republican front-runner (and he’s much more likely to be telling the truth).

    > Snowden: FBI’s claim that it requires Apple’s help to unlock iPhone is ‘bullshit’

  • Handoff for iTunes | iMore

    I’d love to be able to start a playlist on my Mac and then walk away, pick up my iPhone, swipe, and keep right on listening.

    So true. I (and Rene) have been saying this for a few years at least. This is the dream, and it’s sooooo close with Handoff, just not for iTunes.

    > Why I want to see Handoff for iTunes in iOS 10 so ludicrously much | iMore