Excel / VBA blogs from Wise Owl (page 1 of 6)

Showing blogs 1-20 (out of 112)

Loading live web data into Excel without writing VBA code to scrape HTML

Posted by Andy Brown on 21 August 2017

Need to get tables of data into Excel from a website? There's no need to write lots of VBA code to scrape the site - just use Power Query to create a live link to the relevant page(s).

Tags:   Excel | PowerPivot    |    Power BI - Excel | Power Query

Find the many ways to interact directly between Excel and Power BI Service

Posted by Andy Brown on 29 June 2017

You can pull Excel workbooks (or individual data ranges) into Power BI Service, or push them from Excel using a special add-in. This blog shows how to use both approaches, as well as the underwhelming Analyze in Excel feature.

Tags:   Excel | PowerPivot    |    Power BI Desktop | Power BI Service

Three different ways to find the differences between two Excel worksheets

Posted by Andy Brown on 01 June 2017

There are a few ways to find the differences between two Excel worksheets. This blog considers 3 methods (using Excel formulae, using Query Editor and using the Inquire add-in).

The new functions in Excel 2016, including IFS, SWITCH and TEXTJOIN

Posted by Andy Brown on 18 February 2017

Excel 2016 has a few interesting new functions! TEXTJOIN allows you to concatenate all the cells in a range, and IFS and SWITCH provide other ways to make choices.

Tags:   Excel | Formulae

Want to be get at the file path for an Excel workbook easily? Here's how!

Posted by Andy Brown on 26 January 2017

This short blog explains how you can customise your quick access toolbar to display the path of the file that you are currently working on.

Tags:   Excel | General

How to pick out the first and last dates for a given group using DAX

Posted by Andy Brown on 06 October 2016

It's often challenging working out how to use DAX to solve specific problems. This blog shows how to produce a pivot table showing the first and last instances of any category.

Tags:   Excel | PowerPivot    |    Power BI - Excel | PowerPivot

How to get Excel to read out cell values on calculation

Posted by Andy Brown on 26 September 2016

A short blog explaining how you can get Excel to read out values in cells every time that you recalculate a worksheet.

Tags:   Writing VBA | Other    |    VBA macros | Working with ranges

Which Power BI course should you take - Excel Power BI or Power BI Desktop?

Posted by Andy Brown on 22 July 2016

Microsoft are pursuing two separate BI lines at the moment: one for the Power BI tools which run within Excel, and another for the standalone application called Desktop Power BI. This blog attempts to explain the differences between two applications with such confusingly similar names!

Tags:   Excel | PowerPivot    |    Power BI - Excel | General    |    Power BI Desktop | General

Referring to a range using square brackets

Posted by Andy Brown on 17 December 2015

This short blog shows that there is a third way to refer to a range, other than using the RANGE or CELLS keywords.

Tags:   Writing VBA | Other    |    VBA macros | Working with ranges

This blog explains the lesser known but better Excel VBA InputBox function

Posted by Andy Brown on 17 December 2015

There are two ways you can display an input box using Excel macros: the standard VBA InputBox function, or the much better (but lesser known, at least by this blog's author) Application.InputBox function.

Tags:   Writing VBA | Other    |    VBA macros | Input/message boxes

Disabling Screen Updates in Excel VBA

Posted by Andrew Gould on 26 November 2015

You can give your VBA code a performance boost by simply turning off screen updating while your code runs. This blog explains how to achieve this and how to create a basic timer to test the results.

Tags:   VBA macros | General    |    Writing VBA | Other

What's new in Microsoft Office 2016 (including Excel 2016 changes)

Posted by Andy Brown on 07 September 2015

The latest incarnation of Microsoft Office (2016) should be released on 22nd September 2015 (Microsoft are always a bit ahead of themselves with versions). This blog gives a detailed guide to what's new, the biggest changes being in Excel and Outlook.

Tags:   Outlook | General topics    |    Excel | General    |    Musings | Microsoft Office

Installing the Microsoft Date and Time Picker for VBA

Posted by Andrew Gould on 24 February 2015

Some versions of Windows don't include the very useful Date and Time Picker control. This blog explains how to install the mscomct2.ocx file to get it back!

Tags:   VBA macros | User forms    |    Writing VBA | Other

Using DAX Studio to edit calculated fields or measures in Excel

Posted by Andy Brown on 20 February 2015

The editor for calculated fields in Excel (aka measures) isn't the greatest; this blog shows you how to install and use DAX Studio as a substitute editor.

Tags:   Excel | PowerPivot    |    PowerPivot 2010 | DAX

How to insert ActiveX controls after CANNOT INSERT OBJECT message

Posted by Andy Brown on 29 January 2015

When using Excel, we owls have had problems inserting ActiveX objects after recent security updates. Here's a solution which worked for us!

Tags:   VBA macros | User forms    |    Writing VBA | Other

VBA code to create a chart and textbox, and copy them to Word

Posted by Andy Brown on 03 October 2014

Charts in VBA are a bit weird: this blogs highlights some of their foibles, by showing how charts are really just special shapes, which contain shapes of their own!

Tags:   VBA macros | References    |    VBA macros | Working with charts

Programmatically searching websites using VBA

Posted by Andy Brown on 19 September 2014

I've already pinched (with permission!) the ideas of Fergus Cairns to explain ways to run automatic searches on popular websites like Google; this blog gives an update on the same topic.

Tags:   VBA macros | References    |    Writing VBA | Other

Revealing how Excel workbooks are actually stored as zipped XML files

Posted by Andy Brown on 16 May 2014

Did you know that Excel workbooks are really just a collection of XML files, all zipped together? We didn’t either, so we thought we'd tell the world.

Tags:   Musings | Random musings    |    Excel | General

Flappy Bird in Excel VBA Part 13 - Playing Sounds

Posted by Andrew Gould on 24 April 2014

This part of the tutorial explains how to add sounds to the game using a Windows API function.

Tags:   Writing VBA | Flappy Owl    |    VBA macros | Flappy Owl

Flappy Bird in Excel VBA Part 12 - Managing Game State

Posted by Andrew Gould on 24 April 2014

This part of the tutorial implements a state system to make it easier to determine what actions to perform each time the game updates.

Tags:   Writing VBA | Flappy Owl    |    VBA macros | Flappy Owl
This page has 1 thread Add post
04 May 17 at 18:18

Hello,

I'm having troubles in achieving something on excel and I think you are the right guy's to give me a hand with it.

I have an excel sheet that I use for flight simulation and between other things, I want to create a small compus with shapes(arrows) that rotates whenever I add a value to a specific cell. So far I managed to do it but only with a single arrow. When I try to add a second shape it says "Ambiguous name detected worksheet_Change".

I need 2 arrows because one indicates the heading of the aircraft and another indicates the wind.

On VBA I managed to do the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub

This gives me only the rotation for one of the arrows (shapes), do you have any idea how do I add a second one?

https://drive.google.com/file/d/0B6JMHUkV2NKJRTZVUmRBZER6bVE/view?usp=sharing  On this link there's a picture to give you the idea of what I'm trying to do.

If you need any other information from me, please let me know.

 

 

Thank you very much for all the help you can give me.

 

 

Kindly,

 

 

Edgar Freitas

05 May 17 at 07:05

Hi Edgar, that sounds like a cool idea!

The "ambiguous name detected" error occurs when you create multiple procedures in the same scope with the same name. What I imagine has happened is that you've copied and pasted the Worksheet_Change subroutine to create a separate procedure for the second arrow? Sadly, you can't do it this way - you can only have one Worksheet_Change subroutine per worksheet. The simple solution is to add the code for the second arrow to the same subroutine as the first.

I hope that helps and good luck with the project!

05 May 17 at 18:30

Andrew, I can't thank you enough!! It worked perfectly!!! :D

Thank you so much for your help.

 

Have a great weekend.

 

 

Kindly,

 

Edgar Freitas

05 May 17 at 16:40

Hello Andrew,

Thank you very much for your reply. 

That's exactly what happened. I thought I could simply copy and paste it to create new arrows.

Your solution is: "...to add the code for the second arrow to the same subroutine as the first."

As I'm not good with VBA, could you please indicate me how to do this? I already tried but I think it's not the right way of doing it as I got an error saying "Duplicate declaration on the same scope".

This is how I did it:

Private Sub Worksheet_Change(ByVal Target As Range)
    'event raised when value in cell changed BY USER
    
    Dim KeyCells As Range
    Set KeyCells = Range("Z2")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
    Dim KeyCells As Range
    Set KeyCells = Range("Z3")
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
        MoveArrowToA1
    End If
End Sub
Sub MoveArrowToA1()
    'Select shape to move
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z2")
    ActiveSheet.Shapes.Range(Array("seta")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    Dim CellValue As Integer
    Dim CurrentlySelected As Range
    
    Set CurrentlySelected = Selection
    
    CellValue = Sheets("747 lbs").Range("Z3")
    ActiveSheet.Shapes.Range(Array("seta2")).Select
    Selection.ShapeRange.Rotation = CellValue + 180 'Plus 180 since 0 pos is down
    
    'Select prev cell that was in use
    CurrentlySelected.Select
    
End Sub
 

Thank you once more Andrew!

 

Kindly,

 

Edgar Freitas

Andrew G  
05 May 17 at 17:29

Hi Edgar,

In the same way that you can't declare two subroutines with the same name in the same module, you can't declare more than one variable with the same name in the same subroutine.  Simply remove the duplicate Dim statements so that you don't have more than one variable with the same name in a single procedure.

I hope that helps!