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

Showing blogs 61-80 (out of 112)

Using Excel Add-Ins to Share VBA Code

Posted by Andy Brown on 05 March 2012

If you've written a killer function, you'll want to be able to share it between workbooks. The best way to do this is using an add-in.

Tags:   VBA macros | References    |    Writing VBA | VBA tutorial

Protecting Worksheets and Workbooks in Excel

Posted by Andrew Gould on 28 February 2012

If you've ever been annoyed by somebody else changing parts of your Excel workbooks it's a good idea to learn about protection to prevent them from doing it again!

Using Data Validation in Microsoft Excel

Posted by Andrew Gould on 28 February 2012

If you're constantly frustrated by other people entering the wrong values in your spreadsheets why not learn how to use data validation to prevent them? This blog series explains how you can control what other people can do to your spreadsheet.

Advanced controls in userforms with VBA macros

Posted by Andy Brown on 28 February 2012

The two previous parts of this mini-blog have shown how to draw forms and how to write code to handle form events. This final part shows how to add some of the more exotic controls to user forms, like combo boxes, list boxes, multipage controls, spinners and option buttons.

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

Writing VBA macros to work with user forms

Posted by Andy Brown on 28 February 2012

The previous part of this three-part series showed how to draw user forms; this part shows how to write macros to get them to work (the final part shows how to use some of the more advanced controls, such as combo boxes and MultiPages).

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

Drawing UserForms in the VBA code editor

Posted by Andy Brown on 28 February 2012

Learn how to create your own custom dialog boxes in VBA, using UserForms. This is the first part of a three-part series (the other parts are on Writing Code for UserForms and Creating Advanced Controls).

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

Using Scenarios in Microsoft Excel

Posted by Andrew Gould on 27 February 2012

Scenarios are incredibly useful when performing "what-if analysis". They allow you to quickly swap a range of input values to test different possibilities without having to retype any values. Read on to find out how they work!

Tags:   Excel | What-if analysis

Goal Seeking in Microsoft Excel

Posted by Andrew Gould on 24 February 2012

Goal seeking in Excel allows you to approach calculations from the "result end" and get Excel to tell you what inputs you need in order to hit a specific target. Read on to find out how it works!

Tags:   Excel | What-if analysis

Writing functions for Excel using VBA

Posted by Andy Brown on 24 February 2012

On-line training in writing functions using VBA, for use either within other VBA programs or within Excel itself.

Arrays in Visual Basic for Applications macros

Posted by Andy Brown on 24 February 2012

You can use arrays (multiple variables) in VBA to speed up code - and they are also invaluable for processing CSV files and the like.

Using enumerations in VBA

Posted by Andy Brown on 24 February 2012

Excel and other MS Office applications make extensive use of enumerations - and you should too!

Tags:   Writing VBA | VBA tutorial    |    VBA macros | Enumerations

Using Styles in Microsoft Excel

Posted by Andrew Gould on 24 February 2012

Constantly formatting cells in the same way can be one of the most tedious and time-consuming tasks in Excel. This blog teaches you how to use styles so that you can quickly apply consistent formatting across a range of cells.

Tags:   Excel | Formatting

Writing recursive programs in VBA

Posted by Andy Brown on 20 February 2012

In certain cases in VBA (displaying the contents of folders, listing hierarchical data) writing a program which calls itself is by far the easiest way to go - this blog gives worked examples of 3 such recursive programs.

Tags:   VBA macros | General    |    Writing VBA | VBA tutorial

Passing arguments to subroutines in VBA

Posted by Andy Brown on 09 February 2012

To become an efficient programmer, you need to stop writing one long subroutine and start writing lots of small ones instead - and to do this, you're going to need to know everything there is to know about passing arguments.

Linking to ADO databases with VBA

Posted by Andy Brown on 06 February 2012

Learn how to write VBA macros to add, edit and delete data in Access, SQL Server and other databases, using something called ADO.

Tags:   VBA macros | References    |    Writing VBA | VBA tutorial

Copy, rename, delete and move files and folders

Posted by Andy Brown on 23 January 2012

By referencing the unintuitively named Microsoft Scripting Runtime object library you can write VBA code to access files and folders on your hard disk. This blog explains how, and gives a couple of worked examples.

Reading and writing to/from a text file using TextStreams

Posted by Andy Brown on 13 January 2012

The easiest way to work with text files in VBA is using TextStream objects - this blog explains how they work.

How to choose files and folders in VBA

Posted by Andy Brown on 13 January 2012

To make it easier for users of your VBA systems to choose files, you can show FileDialogs on screen. This blog explains what these are, and how to customise them.

Linking to the Microsoft Scripting Runtime library

Posted by Andy Brown on 13 January 2012

In order to work efficiently with files and folders you first need to create a FileSystemObject. This blog explains how to do this!

Creating references programmatically in VBA

Posted by Andy Brown on 03 January 2012

Did you know that you can create and manage references from within VBA code? This article gives you ideas on how to do just that.

Tags:   VBA macros | References    |    Writing VBA | VBA tutorial
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!