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.

Purple $2 Coin

I saw my first $2 coin today in the wild! Was sitting in the tip pile at a little coffee shop at Surfers Paradise on the Gold Coast called Duo Deli Cafe. I really should hit them up one time, the food there looked great and the coffees were pretty good too. Sorry I couldn't snap a shot of the actual coin. Would have been a bit awkward flipping out the ole iPhone5 for some Pressgram action while at the register haha. Glad to see it out and about though. Thought I'd never see it. Probably explains why they are going for over $10 on eBay.

Published via Pressgram

EOM (Enterprise Output Management) to Enable Sales Teams

Recently I discovered Enterprise Output Management on Yammer after a conversation with someone that has been implementing this practice into their business. I have to be honest, at first I did not know what EOM was and after some Googling only came across "End of Message". Embarrassed why I did not know this new acronym I switched out the 'E' for Enterprise and researched. Enterprise Output Management was the answer and the first result in Wikipedia spoke of the flow of information from system to system within an organisation. Upon reflection I discovered that this is precisely the philosophy that I have been trying to implement in the last few months in our sales team.

From my understanding, Enterprise Output Management is the linkage between one system within an organisation and another. For my circumstance I am focussing on the linkage between systems across divisions to the sales environment. If you are in the production environment this may mean linking production of a customers product to their order. If you are in a credit environment this would be the linkage of a customer credit information such as exceeding their limits also with an order. In a distribution environment it would be the linkage between a customers delivery in full and on time (DIFOT) to the status and performance against their account metrics.

Why is this important? For traditional account management, information has not always been a key driver of action. Most account managers in the past are running blind until they reach the end of the month for some sort of performance report to be sent out to them to give them an indication of their performance. For the account managers that are on top of their game they may track these sort of metrics individually but ideally you don't want them to focus on these areas as it doesn't add value to the customer. In a sales environment where we are trying to add value, we must provide the account manager with the information that is relevant to allowing them to fully understand both the market and the customer. An example of this is giving them metrics that track market pricing. If an account manager is confident that metrics give a clear indication of market pricing then they are confident when negotiating with a customer and setting customer pricing. If an account manager is provided with DIFOT or quality information metrics that relate to the organisations average performance for like customers than the account manager can be confident when dealing with service requests based on quality or missed deliveries. Confidence within an account manager leads to confidence within the customer.

EOM also leads to a reduction in the amount of time it takes for an account manager to service a customers request for information. Each delay in finding an answer for a customer is a delay in the customer beginning their next task or action. In most cases if the delay is too long they will drive forth without an answer and place blame at a later time if the risk converts to a complaint or issue.

I am excited to be able to place a label on what I have been achieving lately as it will allow me to delve further into method of achieving this output at a high quality. At the moment I do this through Microsoft Excel, dipping from one information system to another pulling table after table of metrics. If the tables have a unique identifier within each table that is common across all then I have the potential to create the link and analyse the relationships. The next step is then to present the relationship in a way that allows the account manager to consume the information within 30 seconds or less which gives them more time spent on the commercial or strategic decision.

I'll post more on EOM later as I discover the tools and techniques that I use to implement this in our sales environment. Here's to getting data at the blink of an eye to the people that matter most!