The Cell Master.
Suat Mehmet Ozgur
from Istanbul, Turkey
Please introduce your self. You may be as thorough as you wish. Feel free to include or omit any detail about yourself.
My name is Suat Mehmet Ozgur. I am married with My Muge, who is the love of my life. We are living in Istanbul, Turkey.
I am a Civil Engineer with a Master's degree in Mechanics. Started coding with GWBasic around 1989 as a result of the requirements of complex mathematical problems then programming became the biggest advantage during my university years. Coding was not very popular and people had a distance to write codes to accomplish tasks, they said it is wasting of time to learn pages of description of a programming language to print something different on the screen. I should admit that I did read lots of page about programming languages. Unfortunately I couldn't have succeed in explaining people that there was more than Fortran 77 as an environment and finally programming was a requirement of being an engineer, so I decided to not stay in the university although I've always dreamed that, teaching. Finally now they all know it is a requirement, at least students.
By having chance to connect to online world, I started reading mostly VB related problems in technical forums and posting solutions. Then I realized another way of learning: solving other's problems!
I've been working as a MrExcel programming consultant for more than 3 years which is my dream job, creating different solutions for every single project.
When do you remember using Excel for the very first time? Can you remember any specific details from that first time?
I once tried to learn Lotus 123 but it was no sense to me to write data in cell blocks in 1980s. The first time I needed Excel was to create determination of a large array, 1994 I think (yes, I was that blind with coding to not being able to see easier ways to accomplish tasks). I remember that it was limited with certain dimensions to calculate so I decided to create an unlimited determinant solver in VB, actually I didn't need that sizes but limitations always makes me think for alternatives. But Excel made me impressed, it was the first time I was able to use arrays that easily.
When do you remember writing your first formula or VBA code for Excel?
First formula was a simple =A1+A2 in B1 cell I am sure. I would do the same again if it was the first time today because it is a habit from coding, to test the basic one first.
I met with VBA years later and the first code I created was a simple message box (definetely MsgBox "asd") to see if a module would work like it is in VB, and it did. Then the first object was the "Range" that I saw in the first macro recorder helped code I built. It was selecting and copying a range into another location. I was impressed again, ten times more, when I saw the self-descriptive side of Excel VBA. With macro recorder and help files, there is almost nothing impossible to learn.
On average, how many hours per day do you spend working with Excel formulas and/or VBA code?
Not quite sure, depends on the project that I am working on. May be 3-4 hours in average with Excel.
Which do you find most rewarding to work with: Formulas or VBA in Excel? Please tell us why?
If question was "Which do you find most important to work with" then I would say VBA. Not because I am originally a VB coder before meeting with Excel but it is because you can create even any internal formula by using VBA. It is more creative and you feel unlimited with VBA. It doesn't mean we should create internal formulas in VBA, that would be non-sense. But it is good to know you are not limited.
On the other side, Excel formulas should have to be learned to work in Excel. Excel depends on formulas and it gets its power from formulas first if you are working in Excel to manipulate an existing data.
If you were going to give a novice, just starting out with Excel, some advice, what would it be?
Definetely reading help files. Some people says MS help files are hard to understand. I don't agree, they are the most helpful sources for learning MS products, I am the living sample!
Also you need a project to work on it, without a target, you wouldn't need to discover a method. I always say this, I learned coding in ASP in 2 days, PHP in less time, that's because I had a target. All programming languages executes same algorithms in your mind, you just need to know which one you need..
Join the technical forums, people like helping each other in these places. And be a helper that would provide the project in previous paragraph. It returns a lot more than it takes your time.
Please provide a sample of your first work (either as a formula or vba code) in Excel and tell us about it.
Formula : B1 cell : =A1+A2
I've been told that it is like a calculator but using cells like variables. And I exactly entered by typing this formula to test it. Result was amazing for me.
Then also entering equal sign will make the magic and yes, I noticed that equal sign means something special to Excel, because after typing equal sign and use the arrow keys it was writing cell reference automatically.
VBA: MsgBox "asd"
It sounds funny now but after executing this in a module, I figured that it is just VB! Then the target became creating solutions in a great environment by using VBA to automate daily tasks that would be done in Excel.
My first real work in Excel (functions and VBA combined) was an application that would parse data from big text files and creating reports by filtering data with user-selected predefined items in dropdowns. I built it to import selected text file into a temporary Excel worksheet and fixing this data by exact criterias and finally create the complete data table from this raw data. Then the rest is using functions to retrieve filtered data to create required reports. It was also able to send a copy of created reports as an Excel workbook attachment.
What is your mental attitude when you are preparing to write formulae or VBA code? And what is your working environment?
I mostly don't need computer for coding, I think about the project or problem that I am working on it without PC, mostly when I was trying to sleep. The PC part is the "writing/testing code" phase. However I often need silence while working, better saying, I need certain uninterruption. As I am working from home, I mostly have what I need to focus on a project.
Creating formula: it sometimes needs your creativity. Excel doesn't have all formulas you would need but have formulas that would do the job when they are combined. For example, you need to find the spaces count in A1 cell data but Excel doesn't have a function to accomplish this (I think there is no direct function for this, but I migh be wrong). Try following function:
Especially with VBA you would even not need some addins, for example EOMONTH function, if you want to use your own user defined function then you can create this function easily:
Function EOMONTH(rng As Range)
EOMONTH = DateSerial(Year(rng), Month(rng) + 1, 0)
We should just add some error handlers to avoid passing wrong data.
If there special preparations that must be in place before you can begin, what are they?
Nothing special, I usually wake up at 10:00AM and if there is something to be solved when I switch on my PC, I often forget myself there and suddenly find myself working, then Muge reminds me to have breakfast or Cuma (our kitty!) reminds me that it is playing time for him.
Finally, please give us something to think about - a reminder of your words here; a phrase that has helped you; a link to your own website. Anything that you think is important for the readers to remember.
If you are a coder then you should be already thinking "There is a solution waiting to be found". All you need is time and algorithm to access the solution. CPU does the rest for you.
Do not hesitate to learn something new, we are living in internet and we have all information we need.
Focus on objects and live with them like your co-workers. I always use similar object names in my codes to define them; wrk, sht, rng, fnd...
Read your codes after finishing writing and make sure it sounds like a poetry :-). That's the art side of coding that prooves we are also artists.
Thank you very much!
Thank you very much for answering the questions.
This Black belt is yours...