Förnöjsam pappa: VBA-funktion som passar i Excel-kalkyler med array-formler (24 september, 2012)

Måndagen den 24 september, 2012

I fredags började jag att arbeta med något som jag funderat på många gånger, bland annat den 17 juni 2010.

Programvarupaketet Microsoft Office innehåller ett inbyggt programmeringsspråk som heter Visual BASIC for Applications, kortform VBA.

Det är lätt att skriva program i VBA men jag tycker det är klurigt att komma på hur gränssnitten ska uttryckas. Tyvärr hör det till vanligheterna att jag får problem på grund av att jag missuppfattat datatyper.

Men i dag skrev jag en funktion som passar in i mina kalkyler med arrayformler (i Microsoft Excel). Så här ser funktionen ut:

Function urgent_in_slogan(input_range As Range) As Variant

  Dim nRows As Long
  Dim nCols As Long
  Dim output_array() As Long
  Dim row As Long
  Dim col As Long

  nRows = input_range.Rows.Count
  nCols = input_range.Columns.Count
  ReDim output_array(nRows - 1, nCols - 1)

  For row = 1 To nRows
    For col = 1 To nCols
      If (InStr(input_range(row, col), "urgent")) Then
        output_array(row - 1, col - 1) = 1
      Else
        output_array(row - 1, col - 1) = 0
      End If
    Next col
  Next row

  urgent_in_slogan = output_array

End Function

Inparametern till funktionen är en range (engelska), alltså ett område med celler. Funktionen returnerar en array, vilket väl på svenska kallas vektor.

För exemplets skull har jag gjort funktionen väldigt enkel. Den undersöker var och en av textsträngarna i det ingående cellområdet. Om en cell innehåller ordet ”urgent” sätts motsvarande värde till 1 i (den returnerade) vektorn, sätts värdet till 0.

urgent    lemon    pear              1         0         0
banana    urgent   orange            0         1         0
urgent    urgent   apple             1         1         0

Arrayformler är väldigt användbara i sammanhang då behov föreligger att göra (exempelvis) radvis jämförelse med urval baserade på villkor över flertalet fält där enklare funktioner som COUNT(), COUNTIF() och IF() inte räcker till.

Komplexiteten i det här ämnet är mycket låg. Ändå var det en pärs för mig att komma på hur jag skulle skriva för att uttrycka rätt datatyper i funktionens gränssnitt.

Först bestämde jag storleken på output_array till nRows, nCols, men det går (tydligen) inte  och det beror på att vektorn output_array då får storleken nRows + 1, nCols + 1, eftersom vektorer deklarerade på det här viset indexeras med nollräkning. Exempelvis om satsen

dim output_array(3, 4)

används blir vektorn fyra rader hög och fem kolumner bred med indexen 0 till 3 (rader) och 0 till 4 (kolumner).

Jag är glad över att jag på den begränsade tid jag har till eget arbete av det här slaget lyckats åstadkomma stommen till en funktion som jag kan anpassa efter behov sedan för mina återkommande kalkyler baserade på arrayformler.

Emellertid när jag var färdig med funktionen och dessa timmar av slit med till en början obegripliga fel och följdfel var över, kändes det tomt. Stundens känsla var alltså: Glädje-tomhet, vilket är värt att begrunda, men att ta till poesi vore att gå för långt.

  1. Pingback: Förnöjsam pappa: Återanvände formatet för VBA-funktion som passar i arrayformler (17 januari, 2013) | Mitt liv som förnöjsam pappa

Kommentera

Fyll i dina uppgifter nedan eller klicka på en ikon för att logga in:

WordPress.com Logo

Du kommenterar med ditt WordPress.com-konto. Logga ut /  Ändra )

Google-foto

Du kommenterar med ditt Google-konto. Logga ut /  Ändra )

Twitter-bild

Du kommenterar med ditt Twitter-konto. Logga ut /  Ändra )

Facebook-foto

Du kommenterar med ditt Facebook-konto. Logga ut /  Ändra )

Ansluter till %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.