Some Basic VBA Reminders

1. Case Select

Dim iColour As Long

Select Case (i)

Case 0:

iColour = ChartColourIndex.StandardFill1

Case 1:

iColour = ChartColourIndex.StandardFill2

Case 2:

iColour = ChartColourIndex.StandardFill3

Case 3:

iColour = ChartColourIndex.StandardFill4

Case 4:

iColour = ChartColourIndex.StandardFill5

Case 5:

iColour = ChartColourIndex.StandardFill6

Case 6:

iColour = ChartColourIndex.StandardFill7

Case 7:

iColour = ChartColourIndex.StandardFill8

End Select

2. Check if a chart is a Pie chart

Public Function IsPieChart(oChart As Chart) As Boolean

IsPieChart = False

If oChart.ChartType = xlPie Or _

oChart.ChartType = xl3DPie Or _

oChart.ChartType = xl3DPieExploded Or _

oChart.ChartType = xlBarOfPie Or _

oChart.ChartType = xlPieOfPie Or _

oChart.ChartType = xlPieExploded Or _

oChart.ChartType = xlDoughnut Or _

oChart.ChartType = xlDoughnutExploded Then

IsPieChart = True

End If

End Function

3. Declaring your own colour palette instead of using ActiveWorkbook.Colors

In 2003, you would have done something like this:

ActiveWorkbook.Colors(ColourIndex.Black) = RGB(0, 0, 0) where ColourIndex was an Enum all all the colors you wanted to use.

You can't do this in 2007, so instead you can declare your own palette and use that.

Public Enum ChartColourIndex

Black = 0 'RGB(0, 0, 0)

White = 16777215 'RGB(255,255,255)

StandardFill1 = 1363314 'RGB(11, 20, 20)

StandardFill2 = 9764985 'RGB(121, 0, 14)

StandardFill3 = 16762624 'RGB(10, 199, 25)

StandardFill4 = 36351 'RGB(215, 11, 40)

StandardFill5 = 17517 'RGB(109, 168, 0)

StandardFill6 = 3801318 'RGB(230, 0, 58)

StandardFill7 = 16742898 'RGB(142, 182, 15)

StandardFill8 = 16721958 'RGB(124, 12, 0)

End Enum

Then if you want to grab the colour just use ChartColourIndex.StandardFill1 or whatever one you want. The other option is to set up themes and colours, and reference them.

Some Basic SQL Reminders

1. Send result of SELECT statement to xml output

select * from Table1

FOR XML auto, Type, Elements

If you choose to save the results to file, it will generate an xml file.

2. Try a transaction without commiting

BEGIN TRANSACTION

UPDATE M

SET IsLockedOut = 0

FROM aspnet_Membership M

INNER JOIN aspnet_Users U

ON M.UserId = U.UserId AND M.IsLockedOut = 1

ROLLBACK TRANSACTION

This will try the transaction and then rollback the changes, even if it is successful. Great for trying out statements to see if they work or not, without destroying the database.

3. UPDATE Syntax

UPDATE

Document

SET

CodeId = 22041

WHERE

DocumentID=25

4. INNER JOIN on an INSERT statement

INSERT INTO DocumentGroupSender(reportGroupid, retailerid)

SELECT

DG.id, S.id

FROM Document D

INNER JOIN DocumentVersion DV ON D.currentDocumentVersionId = DV.id

INNER JOIN Sender S ON S.documentVersionId = DV.id

INNER JOIN DocumentGroup DG ON RG.reportId = D.id AND DG.isRetailer = 1 AND DG.name = 'Other'

This allows an insert into a new table using existing values based on a condition (or 3 here).

5. UPDATE statement with an INNER JOIN

UPDATE DocumentGroup

SET documentId = DV.documentId

FROM DocumentGroup DG

INNER JOIN DocumentVersion DV on DG.documentVersionid = DV.id

This allows a table to be updated based on a condition.

6. INSERT data from one table column to a column in a different table

INSERT INTO DocumentGroupCategory(categoryid)

SELECT id

FROM Category

Handy for porting data over form specific columns instead of whole tables.

7. INNER JOIN syntax

SELECT F.FishName, M.MailCodeId, M.FishDescription

FROM Fish F INNER JOIN ColourCode M

ON F.FishId = M.FishId

AND F.Region = M.Region

JOIN FishFamily FF

ON FF.FishFamilyID = F.FishFamilyId

WHERE M.Region = 'CE'

Well, you never know when you might want to select specific colours and families of fish from certain regions..


8. INNER JOIN within a DELETE

DELETE A FROM Amphibian A
INNER JOIN Frog F
ON F.id = '22' AND F.amphibianId = A.amphibianId
WHERE amphibianId = '1'


The "DELETE A FROM.." part is really important.  If this is not included the query will fail.