VBA to adjust a spreadsheets zoom level based on a users monitor resolution

VBA to adjust a spreadsheets zoom level based on a users monitor resolution

I am currenty tweaking this code to make it so that the excel applications I build for our sales team adjust based on the large variation in monitor sizes. If you want to use this code on your own project please follow these instructions.

Once you have accessed your excel document enter the VBA environment.

Accessing VBA in Excel

Press Alt+F11 from within an opened Excel document

Either 'Insert a Module' by clicking the insert button or 'Right-Click' on a worksheet and select 'View Code'

Copy the code below.

Paste the code into the new module or worksheet code area.

Adjusting the VBA code

If you want to adjust the zoom of all worksheets then comment out (by adding a ' before the line) or remove the following lines

If activews = cmws Then
maxWidth = GetSystemMetrics(0) * 0.5

End If

If you want to target a specific worksheet then change the name of the value between Sheets("enter worksheet name here").Name in the variable cmws

Private Declare PtrSafe Function GetSystemMetrics Lib "USER32" _
(ByVal nIndex As Long) As Long

Sub Zoom()
Dim maxWidth As Long
Dim myWidth As Long
Dim myZoom As Single
Dim cmws As String
Dim activews As String

'Set name of the worksheet you want to adjust zoom on
cmws = Sheets("Customer Matrix").Name
activews = ThisWorkbook.ActiveSheet.Name

If activews = cmws Then 'remove this line if you want
maxWidth = GetSystemMetrics(0) * 0.5 'remove this line if you want
Else 'remove this line if you want
maxWidth = GetSystemMetrics(0) * 0.95
End If 'remove this line if you want
myWidth = ThisWorkbook.ActiveSheet.Range("R1").Left
myZoom = maxWidth / myWidth
ActiveWindow.Zoom = myZoom * 100
End Sub

A Preview of the Microsoft Power BI for Office 365 Preview

That's right... it is a preview of a preview. I have been working recently building Excel applications on local drives with a target user base that needs to be connected to our intranet to access those drives. This is cumbersome and requires the client of the application to come off the road and into the office to perform their analysis that the application provides. In large corporations there isn't a lot you can do about this due to IT roadblocks (I say roadblocks but I know they are just trying to keep things secure) and user skill levels.

As the global cloud technology advances at a rapid pace it grows more obvious everyday that by the time IT organisations deploy new technologies, some Start Up somewhere will be putting the pressure on with a better product that does things more intuitively, easier and with less requirement for skilled analysts. To stay ahead of the curve I have been doing a lot of my own research in how to utilise the web through apache and MySQL databases to bring the data to the user on their devices (usually brought in from home) so that I am not limited by IT to bring the system to the user. It is an odd rationale to determine whether I am overstepping my scope but from my perspective I am just utilising these technologies to have easier access to my clients information for my own purposes.

Take for example forms that are used for administrative or data collection purposes. Usually built in Excel, deployed through local drives and usable by people connected to those drives. But what if I was to build a form into a WordPress site self hosted by me on my computer or through a secure server paid by me. What would be the limitation on doing this? I am not too sure. Especially considering the growing amount of employees utilising services such as MailChimp, Survey Monkey or Hootsuite that provide freemium benefits with little to no setup time. We do this because we can self manage these systems, they usually provide great training and we don't have to write business cases or rely on skilled labour to build an in-house solution that costs thousands of dollars.

While stumbling across this process of how I utilise the internet to gather information from my colleagues I came across Microsoft Business Intelligence for Office 365. This is pretty exciting stuff. At first I was just looking at how I can connect Microsoft Excel to my MySQL Database on my server or doing the same thing with Microsoft Access. What I see with Microsoft Power BI for Office 365 does a hell of a lot more than that. What about connecting to any database of information right from within Excel and all is required is a simple search. Need building approval data, just search for it, if a business has setup the information (usually Government agencies) then you just plug yourself in and off you go. No need for manual manipulation of data, running Macros, integrating Javascript and PHP into Excel through .Net to try and scrap databases online. This brings a whole new world of convenience to developing models and forecasting production. For example, plug data around average rainfalls into your production forecasts and see what it's effect will be on gross margin. Now you can have an understanding when it comes to setting budgets etc especially if that database is fairly accurate on forecasting future events.

Now if you are an analyst like myself, you may wonder what work this will leave for actual 'analysis'. From my perspective this, more intuitive design of information and databases allows us to be more creative in our identification of opportunities, rather than spending the bulk of our time just trying to visualise the data for easy interpretation.

For now, I will give Microsoft Power BI for Office 365 a spin, to see what the future is like and be ready for when it comes knocking. Check out the video to see what sort of opportunities will be coming your way.

Zprep – Gamify Your Life

Kickstarter is always a challenging site to visit. Every so often I lose control of myself and I spend a couple of hours checking out some of the latest projects that are successfully being funded. These are a range of creative projects being developed by startup companies, kids in basements or your desk jockey's looking to find some greater purpose in life or discover their dream. Why is this such a challenge? Because the talent pool is so vast that you can't really enter and leave the site without being tempted into pledging to a project.

Zprep - Gamify Your Life

Today's project that has caught my eye is Zprep by a Las Vegas and Brisbane, Australia company called LimbicThinking. Yeah I said Brisbane, how cool is that! Zprep is described as

an App that turns your Daily Life into a Video Game and prepares you for the coming Zombie Apocalypse.

This is a wild concept! Coming from a Gen Y gamer kid who has grown up obsessed with digital worlds such as Hyrule (Legend of Zelda) and open landscapes such as those found in the Grand Theft Auto series. Those world's were always full of an excitement that you just couldn't find at school or on the streets (without getting into trouble). An entire generation of people (myself included) are coming into the world as key drivers to consumer demand. Thanks to the internet we have been able to organise our desires and start producing products that meet these needs. And there is a huge demand for giving people a purpose where no purpose exists. What Zprep aims to do is take all those digital kids sitting on couches and bring to them that same excitement that they find in digital games, but in the real world.

Reader - "WTF did you just say Jay?" Do you mean to say that these people could find the same thrill that they seek in a digital world but in the real world?

Jay - "... yeah that's what I said.

Voice in your head - "Man that is tight!"

Zprep Kickstarter Campaign Video

For a demo check out this cool vid.

What I like about this project, besides that it is a Brisbane company driving it is that it brings a level of safe chaos. Chaos is a huge motivator to getting off you butt and getting something done. So whether it is sitting in front of a screen playing Nintendo, Playstation, Ouya or on your iPhone you ultimately are wasting opportunity to get something done in the physical world as a trade off for the mental satisfaction that can so easily be achieved in the digital world. Also as the power of consoles increases and developers strive to make games more realistic, doesn't it make sense to just shortcut all that and bring games to real life that have the same motivators and story lines as the digital games.

Zprep Redefines How We Play in the Real World

Zprep gives all this same mental satisfaction but in the physical world by what they call "gamifying your life". Gamification is a growing trend driven by the rise of video game kids in the consumer world.

Now the kicker, is this Kickstarter project going to ultimately get funded, succeed and redefine how we play games in the physical world (goodbye tiggy, bullrush and hide 'n' seek). I think it may be unlikely, only because the game looks like a huge interuption to what you are doing at that point in time. I can't really afford to be running out of my office at lunchtime to get to my car (for some reason they do this in their promo video without showing what was achieved). Also I think some of the technology portrayed in the video is misleading to the actual capability of our devices (running through a carpark layered with concrete, your phone ain't going to pick up your location) or doing squats at the traffic lights and getting xp. These are romantic thoughts, but only limited by today's devices. I think in the long run this could work though. Picturing people running around basketball courts with Oculus Rift style consoles strapped to our head allowing us to do things like play virtual Quidditch in the real life. Breaching the gap between imagination and what the eyes and ears actually sense.

Zprep takes us away from thinking of a future that looks like this

Final Thoughts of Zprep

So what do you think? Is gamification of our life going to bring a new perception to how we play in the real world? Is this going to bring a new breed of kids that play virtual video games and get super fit because it makes them so active? Whatever happens, they are the sorts of disruptive technologies that redefine our purpose in life. Once again, yay to the internet for bringing these creative solutions to tackling the drab of our modern life.