Excel / VBA blogs from Wise Owl (page 2 of 7)

Showing blogs 21-40 (out of 125)

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 | 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

Flappy Bird in Excel VBA Part 11 - Detecting Collisions

Posted by Andrew Gould on 24 April 2014

In this part of the tutorial you'll learn how to make the game detect collisions between the bird and the obstacles.

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

Flappy Bird in Excel VBA Part 10 - Creating Obstacles

Posted by Andrew Gould on 24 April 2014

This part of the tutorial shows you how to add obstacles to the game and how to make them move across the screen.

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

Flappy Bird in Excel VBA Part 9 - Creating the Game Sheet

Posted by Andrew Gould on 24 April 2014

This part of the tutorial explains how to generate a game worksheet using code to define the playing area.

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

Flappy Bird in Excel VBA Part 8 - Using Class Modules

Posted by Andrew Gould on 24 April 2014

This part of the tutorial introduces the concept of class modules and shows you how to use them to organise your code.

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

Flappy Bird in Excel VBA Part 7 - Creating Sprites

Posted by Andrew Gould on 11 April 2014

This part of the tutorial describes how to draw simple images using a worksheet as the canvas. You'll also see how to include the images in the game.

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

Flappy Bird in Excel VBA Part 6 - Detecting Player Input

Posted by Andrew Gould on 11 April 2014

This part of the tutorial describes how to respond to keys pressed by the player. You'll learn about the Application.OnKey method and the GetAsyncKeyState Windows API function.

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!